How to separate names in Excel: Split first and last name into different columns

Related Stories

Split full names with the Text to Columns feature.

The simplest way to split names in a column that follows the same pattern, such as only first and last names or first, middle, and last names, is as follows:

1. Select the column of full names you want to separate.
2. Head to the Data tab > Data Tools group and click Text to Columns.



3. On the first step of the Convert Text to Columns Wizard, select the Delimited option and click Next.



4. On the next step, select one or more delimiters and click Next.
In our case, different parts of names are separated with spaces, so we chose this delimiter. The Data preview section shows that all of our names are parsed just fine.




Tip. If you are dealing with names separated with a comma and space, like Anderson, Ronnie, check the Comma and Space boxes under Delimiters, and select the Treat consecutive delimiters as one checkbox (usually selected by default).



5. In the last step, you select the Column data format and destination and click Finish.
The default General format works nicely in most cases. As the Destination, specify the topmost cell in the column where you want to output the results (please keep in mind that this will overwrite any existing data, so be sure to choose an empty column).

Done! The first, middle, and last names are divided into separate columns:

Separate name in Excel with Flash Fill

Excel's Flash Fill can swiftly fill data with a particular pattern. Do you realize that it can also split data, though? This is how:

1. Next to the column containing the original names, create a new column and fill in the first cell with the name portion you wish to extract (in this case, the first name).
2. In the second cell, begin by entering the first name. Excel will automatically fill the initial names in the other cells if it notices a trend, which it usually does.

3. Now, you only need to press the Enter key.

Tip. Usually, the Flash Fill feature is enabled by default. If it does not work in your Excel, click the Flash Fill button on the Data tab > Data tools group. If it still doesn't work, go to File > Options, click Advanced, and ensure the Automatically Flash Fill box is selected under Editing options.