To divide names into two columns in Excel, select the name cells, choose Data > Text to Columns, select Delimited, check Space, and finish the wizard with an empty destination column beside your data. This works best when every name has exactly two parts. If names include middle names or compound surnames, decide how you want to split them first; a formula can put the first word in one column and all remaining words in the other.
Splitting a full-name column makes it easier to sort, filter, address, or combine records by first and last name. Before changing the data, make a copy of the name column or choose a separate destination for the results. A space-based split cannot tell which words are middle names, surnames, or parts of a compound name, so check a few representative entries before applying it to a whole list.
What You’ll Need
- An Excel workbook containing names in one column, such as A2:A100, with a header in A1 if applicable.
- At least two empty columns for the split results, or a copy of the original data so you can preserve it.
- A clear rule for names with more than two words: for example, put the first word in the first column and all remaining words in the second.
Step-by-Step
- Check how the names are written. Look down the list and note whether entries consistently contain two words, such as “Ava Chen,” or whether some include middle names, initials, titles, or compound surnames. A space delimiter separates every word, not just the first and last name. Choose a few unusual entries to use as a test before splitting the entire list.
- Make a safe copy or choose an output location. If the full names are in A2:A100, copy that range to another worksheet or leave the originals in column A and place the results in empty columns B and C. Text to Columns can replace the selected source data if you accept its default destination. Keeping the original makes it possible to compare results and recover the source if the split is not what you wanted.
- Select only the cells that contain names. On Excel for Microsoft 365 for Windows, select A2:A100, leaving the header out of the selection. If you select the header too, Excel will try to split it like a name. Avoid selecting entire adjacent columns if they contain other information. Confirm that the first and last cells in your selection match the actual list.
- Open the conversion wizard. Choose Data > Text to Columns. In the wizard, choose Delimited, then select Next. Delimited means Excel will split each entry wherever it finds a separator you specify. The other choice, Fixed width, is intended for values whose parts line up at set character positions, which is not usually appropriate for names.
- Choose the space delimiter. Check Space in the Delimiters section and look at the data preview. For a simple entry such as “Ava Chen,” the preview should show Ava in one field and Chen in the next. If names have extra spaces between words, check Treat consecutive delimiters as one so repeated spaces do not create empty fields. Leave other delimiters such as Tab, Comma, and Semicolon unchecked unless they genuinely separate parts of your names.
- Set a destination that will not overwrite data. In the final wizard screen, set Destination to the first empty output cell, such as $B$2, if the original names are in A2:A100. Make sure columns B and C, and any further columns the split might use, are empty. If you leave the default destination, Excel writes the first result over the selected source column. Select Finish only after confirming the preview and destination.
- Review the resulting columns. For a two-word entry, column B should contain the first word and column C the second. Check entries at the top, middle, and bottom of the list, along with any names you identified as unusual. If a row has a middle name, Text to Columns may put it in an additional column. If you chose Space but the preview did not split, return to the delimiter screen and confirm that Space is checked.
- Choose what to do with the original names. Keep column A until you have verified the output. If the split is correct, you can retain the full name for reference, hide the original column, or remove it if your workflow requires that. If the results are wrong, undo immediately or restore your copy and repeat the process with a better rule. Do not delete the source just because the first few rows look correct.
For names with middle names, use a first-word-and-remainder split instead. This keeps everything after the first space together in the second result column. In Microsoft 365 or Excel 2024, the TEXTBEFORE and TEXTAFTER functions can do this without creating a separate column for every word.
With full names in A2:A100 and the output columns B and C empty, enter this in B2 to return the first word (for example, Maria from “Maria Elena Lopez”): =IFERROR(TEXTBEFORE(TRIM(A2)," "),TRIM(A2)). Fill the formula down through B100.
With the same layout, enter this in C2 to return everything after the first space (Elena Lopez in the example): =IFERROR(TEXTAFTER(TRIM(A2)," "),""). Fill it down through C100. TRIM removes leading and trailing spaces and reduces repeated spaces between words, while IFERROR provides a sensible result for a one-word name. These formulas split at the first space; they do not identify a person’s legal first or last name. Check the results against your naming rule before using them.
Common Mistakes
- Overwriting the source names: The Text to Columns wizard defaults to the selected column as its destination. Set a separate destination, such as $B$2, or make a copy first. Also check that enough output columns are empty; a three-word name can create more than two fields.
- Assuming every space marks the same boundary: A split on spaces separates all words. “Jordan Lee” becomes two fields, but “Jordan Michael Lee” becomes three. Decide whether the second column should hold the remainder or whether you need a more specific name-cleaning rule.
- Leaving other delimiters selected: If Tab or another delimiter is also checked, Excel may split entries at those characters too. Use only the separator that actually divides the names.
- Including a heading in the selection: A heading such as Full Name may be separated along with the records. Select the data rows only, or handle the heading separately.
- Removing original data too soon: A few correct-looking rows do not guarantee every entry was split correctly. Keep the source until you have reviewed unusual names and the full output.
Tips
- Test on a small sample. Copy five or ten representative names to a spare area and try the split there first. Include a two-word name, a name with a middle name, a hyphenated name, and any other format that appears in the actual list.
- Agree on what “two columns” means. If the goal is a first-name field and a remaining-name field, use Text to Columns only for consistently two-word names, or use the formulas above for first word versus remainder. If the goal is specifically given name versus family name, verify the rule for each naming convention; word position alone may not determine it.
- Keep a clean layout. Use clear headers such as First name and Remaining name, and leave a blank column between the source and destination if that helps prevent accidental overwriting. Make sure the destination columns are wide enough to display long names.
- Convert formula results to fixed values only if needed. Formula results update when the source changes. If you need a static snapshot, copy the result columns and use Home > Paste > Paste Values. Retain the original names if you may need to revise the split later.
- Use the formula method where repeatability matters. Text to Columns is convenient for a one-time conversion. Formulas are useful when new rows will be added, because you can copy the formulas down for new names or use them in a structured Excel table.
Troubleshooting
The result has three or more columns. That is expected when a name contains more than two words and Space is the delimiter. If you want the first word in one column and the rest together, undo the conversion and use the TEXTBEFORE and TEXTAFTER formulas instead. If the first split already replaced your source, restore the saved copy before trying again.
Some results are blank or shifted into the wrong columns. Look for repeated spaces or other separators in the original values. In the wizard, check Treat consecutive delimiters as one when extra spaces are causing empty fields. Also confirm that only the intended delimiter is selected. If the data uses commas or tabs between name parts rather than spaces, select the delimiter that matches the data.
The wizard says it may overwrite data, or output is missing. Cancel and inspect the destination columns before continuing. Text to Columns writes across neighboring cells, so existing values in those cells can be replaced. Choose an empty destination such as $B$2, clear only cells you are certain can be cleared, or run the split on a duplicate worksheet.
You see a formula error or the formula is displayed as text. TEXTBEFORE and TEXTAFTER require Microsoft 365 or Excel 2024. In an older Excel version, use Text to Columns for simple two-part names, or use a compatible alternative suited to the exact data. If the formula appears literally in the cell, change the cell format from Text to General, then re-enter the formula. Check that the formula begins with an equals sign and uses straight quotation marks.
Some names are still split incorrectly despite a clean preview. The tool follows the delimiter; it cannot understand name structure. Review initials, prefixes, suffixes, hyphenated words, and compound surnames individually. If your list combines different naming formats, preserve the original full name and apply a documented rule rather than assuming a single space-based split will work for every person.
You are using Excel for Mac or Excel for the web. In Excel for Mac, look for Text to Columns on the Data tab; the wizard’s appearance may differ slightly from Windows. Excel for the web may present commands differently, so if Text to Columns is not available in your interface, use the formula approach if TEXTBEFORE and TEXTAFTER are supported there, or open the workbook in the desktop app.
Frequently Asked Questions
Can Excel split a full name into exactly two columns automatically?
Yes, when each entry has two parts, use Data > Text to Columns and choose Space as the delimiter. If entries have middle names or other extra words, Excel may make more than two columns. Use a first-word-and-remainder formula if that is the result you want.
How do I keep middle names with the last-name column?
Use the formula method: put the first word in one column with TEXTBEFORE and everything after the first space in the other with TEXTAFTER. It produces “Maria” and “Elena Lopez” from “Maria Elena Lopez.” Review names where the first or family name contains multiple parts, because the formula follows position, not a person’s actual name structure.
Will Text to Columns change my original names?
It can. If you use the default destination, Excel writes the first split result into the selected source column. Set a separate destination in the wizard or copy the source data first to preserve the full names.
Can I split names with a comma between them?
Yes. Choose Data > Text to Columns, select Delimited, and check Comma instead of Space. If entries contain both commas and spaces, use the delimiter that separates the fields you intend to create, and inspect the preview before finishing.
Do the formulas update when I edit a name?
Yes. The results recalculate when the source cell changes. If you need unchanging results, copy the formula output and use Home > Paste > Paste Values after you have checked it.
Can I use these formulas in older Excel versions?
TEXTBEFORE and TEXTAFTER are available in Microsoft 365 and Excel 2024, not every older release. For a simple list with exactly two words per name, Text to Columns is a practical alternative. For older versions and names with variable numbers of words, the right formula depends on the split rule you need.
Related
- How to Delete Strikethrough in Excel in a Few Steps
- Excel Calendar With Correct Dates: A Step-by-Step Guide
- Excel AutoSave: How to Turn It Off and Keep Changes Safe
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel AND Statement for Conditional Formatting RulesTo use an AND statement in Excel conditional formatting, create a formula-based rule with the AND function. For…
- How to Hide Multiple Columns in Excel at Once: 3 WaysTo hide multiple columns in Excel at once, select their column letters, right-click the selection, and choose Hide.…
- Excel: How to Consolidate Columns Into One ColumnTo stack values from several Excel columns into one vertical list, use =TOCOL(A2:C100,1,TRUE) in Microsoft 365 or Excel…
- Create Rules in Outlook on the Web: A Step-by-Step GuideTo create a rule in Outlook on the web, open Settings, go to Mail > Rules, select Add…
- Are Excel Worksheets Free? Options, Limits, and CostsYes— you can create and edit Excel worksheets for free in Excel for the web with a Microsoft…
- Excel SUBTOTAL: Does It Ignore Hidden Rows?Yes, Excel’s SUBTOTAL can ignore hidden rows, but the result depends on its function number. Use 101–111 to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.