How to separate first and last name in Excel

Admin, Saturday, August 6, 2022
Rate this post

You can use Excel for much more than managing formulas and data. It is also an excellent way to manage your contacts. When you import all of your contacts from another source, you will likely have their first and last name in one cell. There is nothing wrong with that, but separating the two can help you organize them better. Here are the steps for separating first and last names in Excel.

Read more: How to show columns in Excel

quick answer

To separate your first and last names in Excel, highlight your list of names and select Text to columns. In the popup, choose specific and choose space of options. Once done, Excel should separate all first and last names into different columns.

How to separate first and last name in Excel

We’ll start with the fake contact sheet. You’ll notice that it displays each person’s name, email address, and work. None of these email addresses are real, 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 Names.

separate names excel 1 1200x439 1

Adam Bernie / Android Authority

Now right click and search for file Insertion Selection. Select Insert to add the empty column.

separate names excel 2 1200x452 1

Adam Bernie / Android Authority

Select the top of the column with your names in it. In our example, this means column A. Now make sure you have data The tab is open.

separate names excel 3 1200x445 1

Adam Bernie / Android Authority

Click on Text to Columns button. This should open the wizard’s menu. In the menu, select specific. This defines separate names using tabs or spaces between them.

separate names excel 4 1200x486 1

Adam Bernie / Android Authority

On the second page of the wizard, uncheck 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 Bernie / Android Authority

Go to the third page of the wizard and select ending to apply your changes.

Separate names excel 6 1200x488 1

Adam Bernie / Android Authority

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

Separate names excel 7 1200x444 1

Adam Bernie / Android Authority

Now you know how to separate first and last names in Excel. Your contact lists will never be the same, and it will be easier to search for contacts now. You can also sort your list alphabetically to be more organized.


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

questions and answers

You can use the left function, the right function, and the search function to separate the first and last names. Enter a formula = LEFT(A2, FIND(“”, A2,1) -1) In a blank cell next to the name to get the first name, or enter = RIGHT(A2, LEN(A2) -FIND(“”, A2,1)) In a blank cell next to the name to get the last name.

Rate this post