Splitting full names is a simple task and it takes less a minute to separate first name and last name from a cell.
You may need to manage certain types of data where names of people must be separated into separate adjacent cells.
Steps to separate the first name, middle name, and last name
Let’s get started with this step-by-step guide to splitting full names into different cells.
Splitting full names with Text to Column tool
First, we take a list of full names in a sheet and separate them in Excel-
- Copy all full names and paste them to the First Name column.
- Select the cells with full names in the First Name column.
- Go to Data tab, under Data Tools section, select Text to Columns.
- A new window opens showing Text to Column settings.
- Choose Delimited options to separate characters with commas, spaces, or tabs in a cell.
- Click Next.
- Under Delimiters, tick Space to separate each name with spaces in between and to shift them to adjacent cells.
- You can view the Data Preview below to understand how your data will be separated.
- Click Next and then Finish.
You might see a message which says- “There’s already data here. Do you want to replace it?”. Click OK to proceed.
The full names have now been separated and all first names and last names have been shifted to separate cells at once. This is how your data should look like now-
You can even separate texts to different columns with other delimiters like commas, semicolons, or any custom characters that you prefer which can be entered in the Other section.
Splitting full names with Flash Fill tool
Separating names can be done in an easier way with the help of Flash Fill tool in Excel. Let’s see how we can use this tool to separate first names and last names.
We take the same sample database to learn Flash Fill-
- Flash Fill needs an example of how you want to display your data. For example, separate first and last name in two adjacent columns.
- You will first manually separate the first name and last name of the first person in the list and Flash Fill will learn from your example.
- Now, click on the cell below Mathew, which is the first entry of First Name column.
- Press CTRL+E for both Windows and Mac.
- Or go to Home tab, under Editing section, pull down on Fill and select Flash Fill.
- Repeat for Last Name column.
Your data has now been separated. All first names have been separated from last names in two different cells.
Conclusion
This was a simple step by step guide to separate first names and last names to different columns explained by two methods. If you have any questions regarding separating texts from cells, drop a comment and we will answer your queries!