Excel can separate full names into different columns with Text to Columns. This works best when the names follow a consistent pattern, such as “First Last.” More complicated names may need extra review because spaces can also occur in middle names, compound surnames, and suffixes.
How to Separate Names in Excel
Step 1: Make room for the separated names
Check the columns to the right of the full-name column. Text to Columns can place the split values into neighboring cells, so insert empty columns first if those cells already contain information you need to keep.
Step 2: Select the cells containing the names
Select the full-name cells you want to split. You do not need to select the entire worksheet.
Step 3: Open Text to Columns
Select the Data tab, then choose Text to Columns.
Step 4: Choose Delimited
Select Delimited and choose Next. A delimiter is the character Excel will use to decide where one piece of the name ends and the next begins.
Step 5: Select Space as the delimiter
Choose Space. The preview will show how Excel plans to divide the names. If your data uses commas instead, such as “Last, First,” choose the delimiter that matches the actual list.
Step 6: Choose the destination
Review the destination before finishing. If necessary, specify an empty starting cell so Excel does not overwrite other data.
Step 7: Select Finish
Choose Finish to split the names into separate columns.
Why Some Names Need Manual Cleanup
A space-based split does not understand which words are first names and which are surnames. For example, a person with a middle name can produce three columns, while a compound surname can be divided when you wanted it kept together. Review the results before using them for mail merges, account records, or other important tasks.
Using Flash Fill Instead
Flash Fill can be useful when you want Excel to recognize a pattern. Type the desired first name beside the first full name, begin the next example, and use Data > Flash Fill when Excel recognizes the pattern. Repeat the process for last names. Always inspect the results because pattern recognition can make mistakes with unusual names.
Additional Information
For a one-time cleanup, Text to Columns is usually the simplest approach. If the source names will change regularly, formulas or Power Query can be better because they can be refreshed instead of repeating a manual split.
Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.
His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.