[2023] How to Split Names in Excel with Easy Formula
Prologue
In this article, you’ll learn about the formula to split names in Excel.
If you split names, you can sort the data according to the first, middle, or last name. Additionally, you can create email addresses based on the names.
The article is divided into four main parts. In the first three parts, you’ll see how to split the first, middle, and last names, respectively. Finally, you’ll know about 2 alternatives to the formula to split names.
Formula to Split First Names in Excel
There will be 4 formulas to split the names and return the first name from them. Firstly, we’ll combine the LEFT and FIND functions. Then, we’ll combine the LEFT and SEARCH functions. After that, we will merge the MID and FIND functions. Lastly, you’ll see the usage of the new TEXTBEFORE function to split first names.
Combining LEFT and FIND Functions
In this first method, you’ll see the merge of the LEFT and the FIND functions to split the first names in Excel. Type the following formula in cell C3.
=LEFT(B3,FIND(” “,B3,1)-1)
This FIND portion searches for the first position of the blank space. Then, the LEFT function returns the number of characters up to one less than that position.
Merging LEFT and SEARCH Functions
The LEFT and the SEARCH functions can be combined to create a formula to split names. This will be used here to split the first names. Insert the following formula in cell C4.
=LEFT(B4,SEARCH(“-“,B4,1)-1)
This formula looks for the position of the first dash in cell B4. Then, it returns the characters up to one less than that position.
Combining MID and FIND Functions
We’ll create a formula using the MID and the FIND functions to split the first names in Excel. You can see there is a Mr. prefix in the full name in cell B5. We’ll devise a formula to ignore that and return the next word. Insert the following formula in cell C5.
=MID(B5,FIND(” “,B5)+1,FIND(” “,B5,FIND(” “,B5)+1)-(FIND(” “,B5)+1))
Decoding Formula
- FIND(” “,B5) 🠞 4.
- This function locates the position of the first blank space.
- This part is repeated two more times.
- FIND(” “,B5,4+1) 🠞 13.
- This part locates the position of the second white space. The starting position is the last blank space position.
- MID(B5,4+1,13-(4+1)) 🠞 “Benjamin”.
- Finally, the formula reduces like this. The MID function returns values from a specific position. The starting position is 5, and the number of characters is 8.
- So. this function returns the 8 characters from position 5 of the name.
- Thus, it returns the first name and ignores the prefix.
Applying TEXTBEFORE Function
Now, you’ll see how to use the TEXTBEFORE function to split the first names in Excel. Type the following formula in cell C6. This formula returns the value before the first space character.
=TEXTBEFORE(B6,” “)
- The following image shows the 4 formulas to split the first name in Excel.
Formula to Split Middle Names in Excel
4 formulas will be demonstrated in this section. Firstly, a formula will be used to get the middle name from a name separated by spaces. Secondly, we’ll find a formula to split a middle name from a dash delimited name. Thirdly, we’ll use a formula to ignore the prefix of a name. Lastly, the combination of the TEXTAFTER and TEXTBEFORE functions will be used to split middle names in Excel.
Merging IFERROR MID and SEARCH Functions
In this first method, we’ll combine the IFERROR, MID, and SEARCH functions to split middle names in Excel. Type the following formula in cell C3 and press Enter to get the middle name from the full name.
=IFERROR(MID(B3,SEARCH(” “,B3)+1,SEARCH(” “,B3,SEARCH(” “,B3)+1)-SEARCH(” “,B3)-1),””)
Decoding Formula
- SEARCH(” “,B3) 🠞 6.
- This looks for the space in cell B3.
- SEARCH(” “,B3,7) 🠞 12.
- This looks for the blank space after the seventh position.
- IFERROR(MID(B3,7,7),””) 🠞 “Grace”.
- The formula reduces to this. The IFERROR function will return a blank string if there is no middle name in the target data.
Combining IFERROR MID and FIND Functions
Now, the formula will be similar to the previous formula. Instead of the SEARCH function, we’ll be using the FIND function. The main difference between them is that the FIND function is case sensitive whereas the SEARCH function is not. Now, use this formula to return the middle name.
=IFERROR(MID(B4,FIND(“-“,B4)+1,FIND(“-“,B4,FIND(“-“,B4)+1)-FIND(“-“,B4)-1),””)
Decoding Formula
- FIND(“-“,B4) 🠞 8.
- This function finds the position of the first dash in cell B4.
- FIND(“-“,B4,9) 🠞 13.
- Finds the position of the second dash.
- IFERROR(MID(B4,9,6),””) 🠞 “Rose”.
- The complex formula reduces to this simple formula at the end. The MID function returns 6 characters from position 9 for the value in cell B4.
Ignoring Prefix and Split Middle Name
Now, we’ll work with cell B5. There is a prefix to the name. We don’t consider the prefix a first name. So, there are only two parts to the name, despite having three words. Therefore, the normal formula will return Benjamin as the middle name for this value.
So, we’ll need to use a complex formula to return blank (as there is no middle name) for this cell. Type the following formula in cell C5 and press Enter. It’ll return an empty string. Don’t worry, we’ll explain the formula after this image.
=IF(AND(IFERROR(FIND(“.”,B5,1)>0,FALSE),LEN(B5)-LEN(SUBSTITUTE(B5,” “,””))<3),””,MID(B5,FIND(” “,B5)+1,FIND(” “,B5,FIND(” “,B5)+1)-FIND(” “,B5)-1))
Decoding Formula
- There is an AND formula with 2 logical arguments.
- FIND(“.”,B5,1)>0 🠞 TRUE.
- This function checks if there is a dot in cell B5. If there is any, then it will be more than zero and return the True value.
- LEN(B5)-LEN(SUBSTITUTE(B5,” “,””))<3 🠞 TRUE.
- LEN(B5) returns the length of the string in cell B5. LEN(SUBSTITUTE(B5,” “,””)), this part removes the spaces and returns the length of the strings in cell B5.
- Then, it finds the difference between them. If the gap is less than three, then we can say the name consists of three parts.
- However, there is a prefix. So, for a name to have a middle name with a prefix, the gap should be 4.
- So, using these two arguments, we can say whether there is a middle name or not.
- After that, we implement the IFERROR function to return False in case there are more than three white spaces.
- IF(TRUE,””,MID(B5,FIND(” “,B5)+1,FIND(” “,B5,FIND(” “,B5)+1)-FIND(” “,B5)-1))
- So, the formula reduces to this. In the case of True output the formula will return an empty string.
- The value_if_false is already discussed twice in the upper portion, so, we’re skipping it here.
Combining TEXTBEFORE and TEXTAFTER Functions
You can combine the TEXTBEFORE and TEXTAFTER Functions to display the middle names from a list of names. Use the following formula in cell C6.
=TEXTBEFORE(TEXTAFTER(B6,” “),” “)
Firstly, the TEXTAFTER portion starts working. It will return the strings after the first blank space. So, it should show “Rose Williams”. Then, we can use the TEXTBEFORE function to return the string before the white space, which means “ROSE”. Thus, this formula returns the middle name.
- The image below shows the summary of the 4 formulas to split middle names in Excel.
Formula to Split Last Names in Excel
Again, 4 formulas will be demonstrated in this section. Firstly, a formula will be used to get the last name from a name separated by spaces. Secondly, we’ll find a formula to split a last name from a dash delimited name. Thirdly, we’ll use a formula to ignore the prefix of a name. Lastly, the TEXTAFTER function will be used to split last names in Excel.
Merging RIGHT LEN and SEARCH Functions
In this first formula, we’ll combine the RIGHT, LEN, and SEARCH functions to split the last name. Type the following formula in cell C3.
=RIGHT(B3,LEN(B3)-SEARCH(” “,B3,SEARCH(” “,B3,1)+1))
Decoding Formula
- LEN(B3) 🠞 20.
- Finds the length of the strings in cell B3.
- SEARCH(” “,B3,1) 🠞 6.
- Looks for the position of the blank space in cell B3.
- RIGHT(B3,14) 🠞 “Anderson”.
- This function returns the 14 characters on the right side of cell B3.
Combining RIGHT LEN and FIND Functions
In this section, the RIGHT, LEN, and FIND functions will be combined to split the last names in Excel. Use the following formula in cell C4.
=RIGHT(B4,LEN(B4)-FIND(“-“,B4,FIND(“-“,B4,1)+1))
This formula is similar to the previous formula. Here, we’re using the FIND function instead of the SEARCH function. The main difference between them is that the FIND function is case sensitive whereas the SEARCH function is not.
Ignoring Prefix and Split Last Names in Excel
Now, we’ll combine the RIGHT, LEN, SEARCH, and SUBSTITUTE functions to create a formula to split the last name from cell B5. Type the formula from below in cell C5.
=RIGHT(B5,LEN(B5)-SEARCH(“%”,SUBSTITUTE(B5,” “,”%”,LEN(B5)-LEN(SUBSTITUTE(B5,” “,””)))))
Decoding Formula
- SUBSTITUTE(B5,” “,””) 🠞 “Mr.BenjaminQuartrek”.
- Replacing spaces with empty strings.
- SUBSTITUTE(B5,” “,”%”,2) 🠞 “Mr. Benjamin%Quartrek”.
- This replaces the second instance of the space with a percentage sign. You can use anything instead of the percentage here. However, make sure that value is not present in your dataset.
- SEARCH(“%”,”Mr. Benjamin%Quartrek”) 🠞 13.
- Returns the position of the percentage.
- RIGHT(B5,8) 🠞 “Quartrek”.
- This is the reduced formula from the complex formula we started with at the beginning. This returns 8 characters from the right side of cell B5 to split the last name.
Using TEXTAFTER Function
Lastly, the TEXTAFTER function can be used to split last names in Excel. Use the following formula in cell B6.
=TEXTAFTER(B6,” “,2)
We’ve entered 2 in the formula to return the strings after the second instance of the white spaces in cell B6.
- The following image shows the formula used in this section to split the last names from the full names.
Other Methods to Split Names in Excel
Now, you’ll get 2 other methods to split names in Excel, but this time without using any formulas. Firstly, we’ll use the Text to Columns wizard. Lastly, we’ll use the Flash Fill to do the same. The data needs to be in the same format. So, you’ll notice a slight adjustment to the dataset for this section.
Using Text to Columns Wizard
In the first alternate method, we’ll utilize the Text to Columns wizard in Excel. Firstly, select your dataset range. Then, select Text to Columns from the Data tab.
- After that, press Next.
- Then, select Space as the only Delimiters and press Next again.
- Finally, set the Destination cell and press Finish.
Using Flash Fill
A pattern needs to be created to split the names in Excel by manually typing. Then, you’ll learn how to use the Flash Fill feature to fill the cells with that user defined pattern.
- Firstly, type the first name in the third row. This will create a pattern for the Flash Fill feature.
- Then, select cell C3 and press Ctrl+E. This will apply the Flash Fill feature to the first name column.
- Similarly, do this a couple of times for the middle and last names.
- Thus, you can split names without using a formula.
Facts About Formula to Split Names in Excel
- The TEXTBEFORE and TEXTAFTER functions are available in Excel for Microsoft 365 version 2208 for Windows and version 16.65 for Mac.
- The Flash Fill feature is only available in Excel 2013 and later.
- We’re using Excel version 16.41 for Mac, this feature is not available on earlier versions for Mac.
Answers to Common Questions
Question: Can I use a single formula to split names in Excel?
ExcelFlash: You can type the following in cell C3 and press Enter in Excel for Microsoft 365 to return the three parts of a name in one go.
=TRANSPOSE(IF(LEN(B3),TRIM(MID(SUBSTITUTE(B3,” “,REPT(” “,LEN(B3))),(ROW($1:$3)-1)*(LEN(B3)+1)+1,LEN(B3))),””))
Question: What is the formula to join names in Excel?
ExcelFlash: You can use several formulas to join the name parts in Excel. The ampersand operator (&), the CONCAT, the CONCATENATE, TEXTJOIN, etc. can be used to join names in Excel. Below, you can see how to join names using the ampersand operator. The formula is placed in cell E3.
=B3&” “&C3&” “&D3
Epilogue
You’ve learned about the formula to split names in Excel. If you’ve any feedback for us, feel free to comment below. You can also follow Excel Flash on Twitter and YouTube.