Press "Enter" to skip to content

How to separate first and last name in Excel

You can use Excel for so much more than formulas and data management. It’s also 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 them better. Here are the steps to separate first and last names in Excel.

Read more: How to unhide columns 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 were hoping to email Luke Skywalker.

We first need to add an empty column that we will use for the last names. To do this, select column B beside the names.

Adam Birney / Android Authority

Now right-click and find the Insert option. Select Insert to add the empty column.

Adam Birney / Android Authority

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.

Adam Birney / Android Authority

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.

Just came in:  Google’s new wired Nest Doorbell is nothing new

Adam Birney / Android Authority

On page two of the wizard, deselect tab and select Space. At the bottom, you should be able to see an example of where Excel will divide your data.

Adam Birney / Android Authority

Move onto page three of the wizard and select Finish to apply your changes.

Adam Birney / Android Authority

Finally, update the column headers to reflect that first and last names are now separate.

Adam Birney / Android Authority

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. You can also sort your list alphabetically to be even more organized.


Read more: How to separate text into columns in Google Docs

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.

Source