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.
Step 2: In the Excel ribbon, go to the Data tab, and then click on Text to Columns.
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.
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.
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.
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:
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”.
To separate full names with a comma, instead of Step 4, choose the Comma separator under the Delimiters list, and then click on Next:
The rest of the steps are identical. This way, your data can more easily be analyzed:
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