How to Separate First and Last Name in Excel

Let’s say you have a spreadsheet with lots of names, and you want to make an analysis of the data.

When the first and last names are together in a cell, it makes it extremely difficult to analyze, sort, and find important information.

To eliminate this difficulty, we will learn in this tutorial to separate first and last names in Excel and also will learn how to separate a name that’s separated with a space, or a comma.

Separate First and Last Name

Splitting a full name into first and last names is extremely easy in Excel with the help of the Text to Columns feature. Let’s learn how…

Step 1: Choose the cells that contain a full name.

Choose cells with full name

Step 2: In the Excel ribbon, go to the Data tab, and then click on Text to Columns.

Click on text to columns in Data tab

The Text to Columns feature splits a single cell into columns when you choose a delimiter (separator) such as a space, tab, or a comma.

Step 3: In the Convert Text to Columns Wizard, choose the Delimited data type, and click on Next.

Choose the delimited data type and press Next

Step 4: Under the delimiters list, choose Space if the first and last names in your data are separated with a space. Then, click on Next.

Choose space as the delimiter and press Next

When you choose space, the Convert Text to Columns Wizard will automatically give you a preview of the separated data under Data preview. This way, you can make sure that the data will be separated correctly.

Step 5: In the last step, you can select the data format of the newly generated columns. In the case of names, the General data format is perfectly suitable.

In addition, you can select the destination of the separated first and last names.

If you don’t specify a new destination, the separation will be made in place (will override your data). Instead, click on the upward arrow under Destination, and choose the destination cell.

Choose destination cell for separated names

Step 6: After completing all steps, click on Finish.

Perfect! Now, all the full names in your spreadsheet will be split into separate columns as first name and last name:

Full name split into separate columns

If your full names are separated with a comma, you only need to change the delimiter you selected in.

Go to Step 4. Let’s assume the format of your full name is: “LastName, FirstName”.

Full names separated with a comma

To separate full names with a comma, instead of Step 4, choose the Comma separator under the Delimiters list, and then click on Next:

Choose comma as the delimiter and press Next

The rest of the steps are identical. This way, your data can more easily be analyzed:

Full name separated with comma split into separate columns

In this tutorial, we learned how to separate first and last names with a space or a comma in Excel with the assistance of the Text to Columns feature.

Also, you may want to check how to combine or switch first and last names in Excel and then sort by last name in Excel.

Related Tutorial: How to remove the middle initial from the full name

Leave a Comment