How to Separate Names in Excel:
Split First and Last Name
Written by co-founder Kasper Langmann, Microsoft Office Specialist.
Microsoft Excel is often used to store data including personal information like names, addresses, contact numbers, etc.
There are times when the names in the data are full names and you would like to split them into first names and last names (sometimes with middle names).
However, users often complicate it by using different functions like ‘SEARCH’, ‘LEN’, ‘LEFT’, ‘RIGHT’, and others.
In this article, we’ll show you 2 easy ways to split full names into first and last names in Excel.
Kasper Langmann, Co-founder of SpreadsheetoIt’s actually simpler than you think! Let’s get started! 😊
Separate names using flash fill
Flash fill is one of the least appreciated tools in Excel. Unbeknown to many people, the flash fill tool will make their lives easier.
What flash fill does is it automatically fills your data once Excel senses a pattern.
By default, the flash fill is active. However, there are times when you have to activate it yourself.
Kasper Langmann, Co-founder of SpreadsheetoFor this tutorial, let’s use this dataset:
Flash fill only works once it senses a pattern. So, let’s try and separate the first full name from the set: “Justine Shaw”.
On the second name, there’s no need to type anything.
Simply put your cursor on C4 and click ‘Data’ from the tab list.
On the ‘Data Tools’ group, click the ‘Flash Fill’ icon.
Immediately, the rest of the cells will be filled out.
Interestingly, you can also press ‘Ctrl’ + ‘E’ on your keyboard to achieve the same results.
Split names using ‘Text to Columns’
If flash fill doesn’t work for you, Excel also has another handy tool you can use to quickly and easily separate names.
The ‘Text to Columns’ tool, to put it simply, separates a text column into multiple columns.
You can say that this tool was made to do tasks like splitting names into first and last names.
Kasper Langmann, Co-founder of SpreadsheetoTo activate the tool, select the names you would like to split. In our example, the range is B3 to B12.
Then, follow these steps:
- Click the ‘Data’ tab from the tab list (like in the previous section)
- Click the ‘Text to Columns’ icon from the ‘Data Tools’ group (beside the ‘Flash Fill’ icon)
Then, a dialogue box will where you’ll have to complete 3 steps.
Step 1:
- Choose ‘Delimited’ from the choices
- Click ‘Next’
Step 2:
- Select ‘Space’ as the delimiter
- Click ‘Next’
If you’re wondering what a “delimiter” is, it’s simply a character(s) that acts as a separator of different parts or regions in a text (like first and last name).
Kasper Langmann, Co-founder of SpreadsheetoIn our exercise file, the delimiter used is a space. If in case you have a comma and space, choose both from the option and tick the ‘Treat consecutive delimiters as one’ box.
Step 3:
- Make sure that ‘General’ is selected as the column data format
- Choose the destination to display the results (C3)
- Click ‘Finish’
Immediately after clicking ‘Finish’, you’ll see the results.
In some cases, you’ll be asked if you would like to continue if there’s already data on the destination.
Be careful since this tool replaces any data you have on the destination.
Wrapping things up…
See? You don’t need complicated formulas to separate full name into first and last names. There are tools in Excel that were made to do just that.
If you’re confused, feel free to download the exercise file of this tutorial. That way, you can follow the steps outlined above. 😊
Kasper Langmann, Co-founder of Spreadsheeto