How to Separate First and Last Names in Excel

Easily organize your data

By

Updated on November 6, 2021

What to Know

Highlight the full names, then go to the Data tab and select Text to Column. Select Delimited, then choose a delimiter set a destination.
Alternatively, select an empty cell and use the LEFT function to get the first name and the RIGHT function to get the last name.
Or, select an empty cell and type the first name of the first record, then go to Data > Flash Fill. In the next cell, repeat with the last name.

This article explains how to separate first and last names in Microsoft Excel. Instructions apply to Microsoft Excel 365, Microsoft Excel 2019, 2016, and 2013.

Separate Names in Excel Using Text to Columns

Excel has multiple functions and features that give you the ability to keep your data organized. For example, you can split first and last names using a feature called Text to Columns:

Open the Excel file that has the data you would like to separate.
Select the data you would like to separate, in this case, the list of Full Names.

If your data includes headers, do not select them, otherwise, Excel will also attempt to separate the data in the headers.
Select the Data tab.
Select Text to Column in the ribbon.
Select Delimited, then select Next.
Select the type of delimiter your data has, then select Next. If your delimiter option is not listed, select Other and enter the delimiter you would like to use in the text field provided.

In our data set, the data is separated with spaces, therefore, we will check the Spaces checkbox as our delimiter.
By default, Excel will overwrite the existing data. If you don't want your data to be overwritten you'll have to modify the Destination value. Select the Destination field and set a destination.
Once you have confirmed your destination, select Finish.
Excel will place your data in the destination cells.

Split First and Last Name Using Excel Formulas

This method is a little more complex, but it also allows more flexibility in terms of separating the data, as you specify exactly what data you will extract using the formula.

In order to achieve the desired data, you'll use left function, the right function, and the search function.

When using formulas, you'll have to use different formulas to split the First Name and Last Name, and it will depend on the format of the original data.

Open the Excel file that has the data you would like to separate.
Select the cell where you'd like either the First or Last Name. For these steps, our data set is formatted like "First Name + Space + Last Name." Therefore, we will use the LEFT function to get the First Name and the RIGHT function to get the Last Name.

If your data set is in a different format or has a different delimiter, then you will have to adjust the formula accordingly.
Enter the formula for the First Name and press Enter.
```
=LEFT(A2,SEARCH(" ",A2)-1)
```
In the next cell, enter the formula to get the Last Name and press Enter.
```
=RIGHT(A2,LEN(A2)-SEARCH(" ",A2))
```
Select both cells with the formulas.
Double-click the bottom right corner of the selected cells. This will extend the formula until the last record of the file.

Split Names in Excel Using Flash Fill

This method is probably the simplest of them all, but it is only available in Microsoft Excel 2016 and later.

This method is not supported in Microsoft Excel 2013 or older versions of Excel.

Open the Excel file that has the data you would like to separate.
Select a cell where you'd like to list the first names and manually type the first name of the first record.
Select the Data tab.
Select Flash Fill.
Excel will automatically populate the first names on the rest of the records of your file.
In the next cell, manually type the last name of the first record of your data.
Repeat steps 3 and 4.
Excel will automatically populate the last names on the rest of the records of your file.

Was this page helpful?

Thanks for letting us know!

Tell us why!