Affiliate links on Android Authority may earn us a commission. Learn more.
How to separate first and last name in Excel
Microsoft Excel is so much more than a spreadsheet app. Its uses go beyond the usual formulas and data management. In fact, it’s an excellent way to manage your contacts. When you import all your contacts from another source, you’ll likely have a first and last name in a single cell. There’s nothing wrong with that, but separating the two can help you organize and filter them better. Here are the steps to separate first and last names in Excel.
QUICK ANSWER
To separate first and last names in Excel, highlight your list of names and select Text to columns. In the pop-up window, choose Delimited and select Space from the options. Once finished, Excel should separate all first and last names into different columns.
How to separate first and last names in Excel
We’ll start with our fake contact sheet. You’ll notice that it shows each person’s name, email address, and business. None of these email addresses are real, just in case you hoped to email Luke Skywalker.
We first need to add an empty column that we will use for the last names. To do this, we need to:
- Select column B beside the names.
- Now right-click and find the Insert option. Select Insert to add the empty column.
- Select the top of the column with your names in it. In our example, that means column A.
- Now make sure you have the Data tab open.
- Click on the Text to Columns button. This should open up a wizard menu.
- In the menu, select Delimited. This identifies the separate names using tabs or spaces between them and select Next.
- On page two of the wizard, deselect Tab and select Space. At the bottom, you should see an example of where Excel will divide your data.
- Move onto page three of the wizard and select Finish to apply your changes.
Now you know how to separate first and last names in Excel. Your contact lists will never be the same, and it should be easier to search for contacts now.
FAQs
You can use the Left function, Right function, and Find function to separate first and last names. Enter the formula of =LEFT(A2,FIND(” “,A2,1)-1) in a blank cell beside the name to get the first name, or enter =RIGHT(A2,LEN(A2)-FIND(” “,A2,1)) in a blank cell beside the name to get the last name.
Yes, Excel has a feature called Text to Columns which can be used to separate names. If you have full names in a single column and you want to split them into first and last names, select the column with the names, then go to the Data tab and choose Text to Columns. In the dialog box, select Delimited, then check Space and click Finish.
For spreadsheets like Google Sheets or Excel, you can use the SPLIT function. If the names are in column A, you could write =SPLIT(A1, ” “) in a new cell. This function splits the contents of cell A1 at each space, dividing the names into separate cells.
In Google Sheets, you can separate text using the ‘SPLIT’ or ‘TEXT TO COLUMNS’ functions. For ‘SPLIT,’ type =SPLIT(A1, “,”) to split text in cell A1 at each comma. For ‘Text to Columns’, select the cells you want to split, go to the ‘Data’ menu, select ‘Split text to columns, and then choose your separator like space, comma, etc.