How to easily separate names in Excel using the Text to Columns feature or formulas. Follow our step-by-step guide to separate first name, middle name, and last name into separate cells in Excel. Improve your Excel skills with this helpful tutorial.
Extract the first name
How to Separate Names in Excel
Assuming the full name is in cell A2, you can use the following formulas:
- To extract the first name:
=LEFT(A2,FIND(" ",A2)-1)
This formula uses the FIND function to locate the space between the first and last name, and then the LEFT function to extract the characters to the left of that space.
Extract the last name
To get the last word from a string is not so easy because what I’ve mentioned earlier, the FIND() function works from left to right so we need to find the last space on the string and get the text to the right of it, Here’s the formula :
=RIGHT(A2,LEN(A2)-FIND(““,SUBSTITUTE(A2,” “,”“,LEN(A2)-LEN(SUBSTITUTE(A2,” “,””)))))
=IFERROR(RIGHT(A2,LEN(A2)-FIND(““,SUBSTITUTE(A2,” “,”“,LEN(A2)-LEN(SUBSTITUTE(A2,” “,””))))),””)
Extract the middle name (if available)
=IFERROR(MID(A2, SEARCH(” “, A2) + 1, SEARCH(” “, A2, SEARCH(” “, A2)+1) – SEARCH(” “, A2)-1),””)
This formula uses the FIND function to locate the first and second spaces, and then the MID function to extract the characters between those spaces. The IFERROR function is used to handle cases where there is no second space (i.e., no middle name), in which case an empty string is returned.
You can enter these formulas in the adjacent cells to the full name, and then drag them down to apply them to the entire column of names.