Microsoft Office Software

How do you separate first and last name in Excel?

separate first and last name in Excel
Break it up: you can split name columns into first and surname

You’ve got yourself a spreadsheet full of people’s names and some idiot has failed to format the spreadsheet columns into first and surname, making it impossible to sort by surname. That idiot was me. Luckily, Excel is one step ahead of us morons, making it easy to split a column full of names into two separate columns with first and last names. Here’s how.

1. Insert a blank column

If you don’t have a blank column to the right of the one currently holding your names, you need to insert a blank one. This is where your surnames will be placed. To insert a column, right click on the letter at the top of the adjacent column (in our case B), click Insert and a blank column will appear.

2. Select Text to Columns

Now click on the letter at the top of your names column (in our case A) and the names should all be highlighted. In the menu at the top of the screen, click the Data tab and then click on Text to Columns.

3. Choose delimited by space

Now we need to tell Excel how to separate the two names. In our spreadsheet, the first and last names are separated by a space. In the pop-up window that appears choose Delimited and click Next.

Then on the next screen choose Space and click Next once more:

4. Finalise the separation

Another screen will pop up, giving you a preview of how your names will be separated. Click Finish.

5. Check your data

Your spreadsheet should now have two columns, one with first names and one with surnames. You will need to adjust the headings on the columns (if you have them) and maybe adjust the cell widths. It’s also worth combing through your data to make sure no errors have been introduced – double-barralled surnames with space in between might need fixing, for example.

NOW READ THIS: How do you convert Excel to CSV?

About the author

Barry Collins

Barry has scribbled about tech for almost 20 years for The Sunday Times, PC Pro, WebUser, Which? and many others. He was once Deputy Editor of Mail Online and remains in therapy to this day. Email Barry at barry@bigtechquestion.com.

1 Comment

Click here to post a comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  • I need to split Last Name, First Name, Middle Name (Initial) that is in one column to 3 columns, First Name, Middle Name, Last Name. Why don’t you include how to do this? I find this same problem with other websites that explain exactly what you said to do but also have the first column with First Name Last Name. I think most companies wouldn’t have a column with First Name, Middle Name, Last Name. They would have Last Name, First Name Middle Initial or Name.