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 a great way to manage your contacts. When you import all your contacts from another source, you’ll probably want 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 display columns in Excel

QUICK ANSWER

To separate first and last names in Excel, highlight your list of names and select Text for columns. Select in the pop-up window Bounded and choose Space from the possibilities. When you’re done, 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 mock contact sheet. You’ll notice that it lists each person’s name, email address, and company. None of these email addresses are correct, just in case you were hoping to email Luke Skywalker.

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

separate names excel 1 1200x439 1

Adam Birney / Android Authority

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

separate names excel 2 1200x452 1

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 Data tab open.

separate names excel 3 1200x445 1

Adam Birney / Android Authority

click on Text for columns button. This should open a wizard menu. Select in the menu Bounded. This identifies the separate names using tabs or spaces between them.

separate names excel 4 1200x486 1

Adam Birney / Android Authority

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

separate names excel 5 1200x491 1

Adam Birney / Android Authority

Go to page three of the guide and choose Exit to apply your changes.

separate names excel 6 1200x488 1

Adam Birney / Android Authority

Finally, update the column headings to reflect that first and last names are now separated.

separate names excel 7 1200x444 1

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 searching for contacts should be easier now. You can also sort your list alphabetically to get even more organized.


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

Frequently asked questions

You can use the Left function, the Right function and the Find function to separate first and last names. Enter the formula for =LEFT(A2,FIND(” “,A2,1)-1) in an empty cell next to the name to get the first name, or enter =RIGHT(A2,LEN(A2)-FIND(” “,A2,1)) in an empty cell next to the name to get the last name.

William

Leave a Reply

Your email address will not be published.