This article will show how to separate the first name, middle name, and last name using Excel formula. To get a quick view of the formulas, consider the following image.
How to Separate First Name Middle Name and Last Name in Excel Using Formula: 3 Ways
It’s easy to split a full name into parts using an Excel formula. But there is no single Excel formula that can extract first, middle, and last names in different cells at a time. In the following dataset, we have a list of the real names of several prominent actors. We will use Excel formulas to extract the first, middle, and last names from their full names (Column B).
Method 1 – Separate the First Name with Excel LEFT and SEARCH Functions
Generic Formula with LEFT Function:
=LEFT(Full Name, Number of characters in the first name)
Generic LEFT-SEARCH Formula:
=LEFT(Full Name,SEARCH(" ",Full Name)-1)
Use comma (,) instead of space if the full names have commas.
Note:
The LEFT-SEARCH formula is more efficient than the LEFT formula in this case for one reason. You have to manually input the number of characters in the first name in the case of the LEFT formula. Whereas the LEFT-SEARCH formula will find how many characters your first names have, then return the first names with the LEFT function in it.
Case 1.1 – Use the LEFT-SEARCH Formula
Steps:
- Insert the following formula in cell C5:
=LEFT(B5,SEARCH(" ",B5)-1)
- Press the Enter key and drag the Fill Handle icon over the cells below to copy the formula in them.
Note:
You can also use the FIND function, instead of the SEARCH function. The difference between them is that SEARCH is not case-sensitive and, hence more flexible, while FIND is case-sensitive. But, both functions will work fine in this case.
Case 1.2 – Use the LEFT Function Only
Steps:
- Count the number of characters in the first name. For example, the character number in the first name of “William Bradley Pitt” is 7.
- Go to cell C5 and enter the following formula into it:
=LEFT(B5,7) - Press Enter and get the first name for the first full name.
- Go to the next cell and repeat all the previous steps.
Method 2 – Separate the Last Name (With or without Middle Names)
Case 2.1 – When There Are Middle Names
Steps:
- Type or copy the following formula in cell C5:
=RIGHT(B5,LEN(B5)-FIND("^",SUBSTITUTE(B5," ","^",LEN(B5)-LEN(SUBSTITUTE(B5," ",""))))) - Hit the Enter button and pull down the Fill Handle icon all the way.
- The final result is shown below.
You can also use the SEARCH function instead of the FIND function.
Case 2.2 – When There Is No Middle Name
Steps:
- Use the following formula in cell C5 and hit the Enter key.
=RIGHT(B5,LEN(B5)-SEARCH(" ",B5))
- Drag the Fill Handle icon to copy the formula down.
- Here is the result.
Method 3 – Separate Middle Names (Single or Multiple)
Case 3.1 – Separate a Single Middle Name with the MID-SEARCH Formula
Syntax of MID Function:
=MID(text, start_num, num_chars)
Steps:
- Insert the following formula in cell C5 and press the Enter key.
=MID(B5,SEARCH(" ",B5)+1,SEARCH(" ",B5,SEARCH(" ",B5)+1)-(SEARCH(" ",B5)+1))
- Drag the Fill Handle icon to cell C9 to copy the formula in all cells.
- See the picture below for the results of our sample.
Note:
If some of the full names have the first and last names only, the formula needs an IFERROR function at the start.
=IFERROR(MID(B5,SEARCH(" ",B5)+1,SEARCH(" ",B5,SEARCH(" ",B5)+1)-SEARCH(" ",B5)-1),"")
Case 3.2 – Separate Multiple Middle Names
Generic Formula to Separate Multiple Middle Names:
=TRIM(MID(name,LEN(first)+1,LEN(name)-LEN(first&last)))
Steps:
- You have to separate the first and last names using formulas (we have discussed these in 1.1 and 2) into two separate columns C and D.
- Copy the following formula in cell E5 and press Enter.
=TRIM(MID(B5,LEN(C5)+1,LEN(B5)-LEN(C5&D5)))
- Drag the Fill Handle and get the output for all names.
3 Ways to Separate First Name, Middle Name, and Last Name in Excel Without Using Formula
Method 1 – Split Full Names with the Help of the Text to Columns Feature
Steps:
- Select the range of cells B5:B9 that contain the full names.
- Go to the Data tab, select the Data Tools group, and press the Text to Columns button.
- A window will pop up. Press the Delimited button and then press Next.
- Mark the Space checkbox and press Next again.
- Select Column data format as General.
- Select the Destination cell C5, where the first output will take place.
- Press Finish.
- If a pop-up appears, press OK.
- The following picture shows the separated names.
Method 2 – Separate the First, Middle, or Last Name Using Flash Fill in Excel 2013, 2016, 2019, and 365
Steps:
- Type the first name in cell C5.
- Select C5:C9 and go to the Data tab.
- From the Data Tools group, select the Flash Fill button.
- The rest of the cells (C6:C9) will automatically return the first names now.
Note:
Create the pattern in cell C5 the way you want to get the output. If you want to remove the middle name, type William Pitt in cell C5, then apply Flash Fill, and so on.
Read More: How to Split Names Using Formula in Excel
Method 3 – Separate Names Using Find and Replace
Case 3.1 – Separate First or Last Name
Separate First Names:
- Copy the full names in a separate column first.
- Press Ctrl + H. The Find and Replace window will appear.
- In the Find what: box, type a space followed by an asterisk (*).
- Leave the Replace with: box blank.
- Press the Replace All button.
- The following image shows the results.
Separate Last Names:
- Copy the full names in a separate column and select them.
- Press Ctrl + H.
- Type an asterisk (*) followed by a space in the Find what: box. Leave the Replace with: box empty.
- Press Replace all.
- The following image shows the results.
Case 3.2 – Remove the Middle Name from the Full Name
- Insert a space, followed by an asterisk (*), then space again in the Find what box of the Find and Replace window.
- Don’t leave the Replace with box empty. This time, you have to insert a space into it.
- Press Replace All.
Quick Notes
- You can use the case-sensitive FIND function instead of the SEARCH function in all the above formulas.
- SEARCH or FIND function returns the position of the searched character from the leftmost corner of the given text string.
- To separate the names using a single method at a time, use the Text to Columns feature.
- To use the Flash Fill feature, create the pattern in the adjacent column. Otherwise, Flash Fill cannot detect the pattern.
Download the Practice Workbook
Related Articles
- How to Split Names with Comma in Excel
- How to Split Names in Excel into Two Columns
- Excel VBA: Split First Name and Last Name
