In this step-by-step tutorial, we are going to take a look at how to split first and last name in excel with detailed examples.
Data mining is cumbersome work!
You need to have a better understanding of various excel text formulas and tools like the Text to Columns to deal with the situation.
If you happen to be the one who lacks these skills, then no worries, you’ll certainly have a better understanding after reading this tutorial.
Guide to Split First and Last Name in Excel
Let me start with a simple example where you just need to separate employee first name in one column & second name in the other column.
This is what our data will look like.
For this example, we will use the built-in Text to Columns option to separate names.
Step 1: Select A2:A10 → head over to Data → Click on Text to Columns
Step 2: Select Delimited & click next
Step 3: Under Delimiter, section → enable ‘Space’ checkbox
Step 4: Click on next → click on Finish
That’s it.
Pro Tip: In case you don’t have space in your data, you can enable Other Delimiter and specify accordingly.
Excel separate first and last name using formulas [Right & Left]
There is no doubt, Text to columns is a great tool to separate names in excel, but it has a drawback.
The drawback is, it’s not a live tool.
Suppose, if your data is constantly changing then you will have to do the same steps again and again.
But…
The good thing is you can automate using excel text formulas.
1) Separate first name using Left formula
In simple terms, Left is a formula to extract user-specified letters from the left side of the text.
For example, to extract ‘Timothy’ from ‘Timothy Fox’ you can write a formula like below:
=LEFT(A2,7)
which means, we are instructing excel to extract the first 7 letters from cell A2 (Timothy Fox). Awesome!
Hang on, there is a problem here.
Since we just have one name we were able to count the total number of letters to extract.
what if you have thousands of names in your workbook?
Can you count? The answer is no!
So, we have a workaround for that.
We will use the FIND() formula to identify the number of letters to extract.
Also read: The Ultimate Guide to Excel Keyboard Shortcuts
How to use FIND formula to count the number of letters to extract
Step 1: Insert FIND formula in the first name section
Step 2: Specify ‘Find Text’ (for this example, we will use blank space)
Step 3: Provide ‘within text’ range. Now you’ll see find formula will return blank space position
Step 4: Now that we know how many letters to extract, let’s add the left formula to separate the first name.
Add left formula before Find like this =LEFT(A2,FIND(” “,A2))
2) Separate last name using Right formula
The other part of this tutorial is to split the last name using the RIGHT formula.
The functionality of the RIGHT formula is similar to Left but the only change is it will extract text from the right side of the text.
For this, we need to use three formulas.
- Find → to find blank space
- Len → to find total letters (including space)
- Right → to split the last name
Find: please follow the same steps as explained above
= FIND(” “,A2)
Step 2:
Len: Insert the LEN formula to count total letters in your text (it will be including any blank spaces/ special characters).
=LEN(A2)
Step 3:
Now subtract Total letters i.e. LEN – FIND (letters till blank space).
=LEN(A2)-FIND(” “,A2)
Step 4:
After the third step, you will have to exact the last name from the right side → add the Right formula like below
=RIGHT(A2,LEN(A2)-FIND(” “,A2))
Wow finally !!
We know how to split first and last names in excel using formulas. Great!
Also Read: How to create Planned vs Actual Chart in Excel
How to separate the middle name in excel using formulas:
I’ve seen a few comments asking how to split middle names using formulas. So here is a tutorial for that.
For example, if we have a name like “Mark Anthony Fernandez” then how will you split?
Step 1:
In this example, we have three names (first, middle & last name), by now you know how to extract the first & last name using Right & Left formulas. So, the only issue is extracting the middle name.
We’ll use a Substitute formula like below to add multiple spaces (for this example I’m adding 50 blank spaces after Anthony).
=SUBSTITUTE(A1,” “,REPT(” “,50),2)
Now, we’ll use the MID formula to extract the middle name like below.
=MID(SUBSTITUTE(A1,” “,REPT(” “,50),2),FIND(” “,A1),50)
Let’s add TRIM formula to remove unwanted spaces.
=TRIM(MID(SUBSTITUTE(A1,” “,REPT(” “,50),2),FIND(” “,A1),50))
After all the steps your result will look like this.
In case you have a better way to extract middle names using formulas please let me know.
Conclusion:
As promised, by now you know how to split first and last names in excel using various methods. For ad-hoc reporting, I’d suggest using text to columns functionality.
In case you are building financial models then it’s a good idea to use above explained formulas to separate names in excel.
Now it’s your turn to apply this knowledge to your work. In case you have any questions/suggestions please let me know.
Stay connected with us via our Twitter