How to Separate Names in Excel (3 Methods)
Learn how to separate names in Excel into different columns using 3 different methods.
Method 1: Flash Fill
If you want to separate a first name from a last name in Excel, the easiest and fastest way is with Flash Fill. Flash Fill is a feature in Excel that automatically fills values in a column based on patterns or examples provided by the user.
For instance, in the table below, if you have a column of names in the format "First name Last Name" and you want to split them into separate columns, you can demonstrate how you want the data split in adjacent columns using a few examples, and Excel will automatically recognize the pattern and fill in the rest of the column accordingly. It's a handy time-saving feature for data cleaning and transformation tasks.
If Excel doesn’t automatically recognize the pattern and suggest filling it, you can manually activate it by going to the data tab, and clicking on flash fill on the right hand side of your Excel file.
Method 2: TEXTSPLIT Formula
The TEXTSPLIT formula splits text into rows or columns using delimiters. The syntax is the following:
=TEXTSPLIT(text, col_delimiter)
As the text, you can select the relevant range, in our case it’s the full name column values. Then, as the col_delimiter, you want to type what you want Excel to separate the values by. In our case, it’s wherever there’s a space. We can do that by tying “ “. That’s a space in quotation marks.
As you can see in the image above, this creates 2 new columns, one with the first name, and another with the last name. The col_delimiter is fully flexible. For example, if the original full name column had a comma between the first and last name, you would change the col_delimiter to “,”. That’s a comma in quotation marks.
Similar to the TEXTSPLIT function, there’s also the TEXTBEFORE and the TEXTAFTER functions which are fairly self-explanatory. The former returns the text before the delimiter, and the latter returns the text after the delimiter.
Method 3: Text to Columns Tool
The Text to Columns tool is another alternative to separate names in Excel into separate columns.
Step 1: Select the full name column.
Sep 2: go to the data tab -> text to columns.
Step 3: Select “Delimited” and click on text.
Step 4: Select the type of delimiter you want to separate by. In this example it’s the space.
Step 5: Choose where you want as the destination for the results.
Additional Resources
For more on Excel, check out our Excel for Business & Finance Course which has helped our students at Goldman Sachs, Bloomberg, Tesla, Amazon, and other top-tier companies.
Other Articles You Might Find Helpful
Ready to Level Up Your Career?
Learn the practical skills used at Fortune 500 companies across the globe.