How to Split Names Using Formula in Excel: 5 Easy Methods

Method 1 – Extract First Name

Steps

Select cell C5 where you want to apply the formula to extract the first name.

Write down the following formula in the formula bar.

=LEFT(B5,SEARCH(" ",B5)-1)

Press Enter to apply the formula.

Drag the Fill Handle icon down the column or double-click on it.

Breakdown of the Formula

SEARCH(” “,B5)-1): Here, the SEARCH function is used to get the blank in cell B5, and -1 is used to get text 1 step before the space.
LEFT(B5,SEARCH(” “,B5)-1): The LEFT function starts text from the left side and finishes it 1 step before the space.

Method 2 – Extract Middle Name

Steps

Select any cell. We select cell D5.

Write down the following formula in the formula bar:

=MID(B5,SEARCH(" ",B5)+1,SEARCH(" ",B5,SEARCH(" ",B5)+1)-SEARCH(" ",B5)-1)

Press Enter to apply the formula.

Drag the Fill Handle icon down the column or double-click on it. You may find some results displayed as ‘#VALUE!’ This happens because two of our given full names have no middle names.

To solve this error and display the middle name as blank when there is no middle name; use the IFERROR function.
Replace the previous formula with the following formula:

=IFERROR(MID(B5,SEARCH(" ",B5)+1,SEARCH(" ",B5,SEARCH(" ",B5)+1)-SEARCH(" ",B5)-1),"")

Drag the Fill Handle icon down the column and you will get the result as blank when there is no middle name.

Breakdown of the Formula

MID(B5,SEARCH(” “,B5)+1,SEARCH(” “,B5,SEARCH(” “,B5)+1)-SEARCH(” “,B5)-1): To get the middle name from the full name, use SEARCH(” “,B5)+1 to extract the name from the next character and place it in the Start_num argument of the MID function. To tell how many characters to extract, you need to subtract the position of the 1st space from the 2nd space position and finally put it in the num_chars argument of the MID function.
If the middle name is missing, the IFERROR function will replace it with an empty string.

Method 3 – Extract Last Name

Steps

Select cell E5.

Write the following formula in the formula bar.

=RIGHT(B5,LEN(B5)-SEARCH("#",SUBSTITUTE(B5," ","#",LEN(B5)-LEN(SUBSTITUTE(B5," ","")))))

Press Enter to apply the formula.

Drag the Fill Handle icon down the column or double-click on it.

Breakdown of the Formula

LEN(SUBSTITUTE(B5,” “,””)): Here, the SUBSTITUTE function is used to eliminate blanks in the full name and count the length using the LEN function without a space.
LEN(B5)-LEN(SUBSTITUTE(B5,” “,””)): This provides the length of space in the dataset.
SUBSTITUTE(B5,” “,”#”,LEN(B5)-LEN(SUBSTITUTE(B5,” “,””))): The SUBSTITUTE function uses here to substitute space with ‘#’.The instance number in the SUBSTITUTE function denotes the position of your instance. Here, we have 2 spaces, and instance number 2 denotes 2nd instance. It replaces the space in the second instance.
LEN(B5)-SEARCH(“#”,SUBSTITUTE(B5,” “,”#”,LEN(B5)-LEN(SUBSTITUTE(B5,” “,””)))): The SEARCH function will search the ‘#’ and return the length value where the ‘#’ appears. Then delete it from the total length of your full name. In our example, it returns 4. This is the length remaining after ‘#’.
RIGHT(B5,LEN(B5)-SEARCH(“#”,SUBSTITUTE(B5,” “,”#”,LEN(B5)-LEN(SUBSTITUTE(B5,” “,””))))): The RIGHT function will extract the return length text from cell B5 which eventually provides the last name of the given full name.

Method 4 – Alternative Ways to Split Names without Using Formula in Excel

1. Utilizing Text to Columns to Split Names

Steps

Select the entire column where you put full names.

Go to the Data tab in the ribbon and select Text to Columns from the Data Tools group.

Select Delimited from Original data Type and click on Next.

Select Space from Delimiters and click Next.

Change the Destination where you want to put your results and click Finish.

This will split the names and place them into two different columns.

Method 2 – Using Find and Replace Command in Excel

2.1 Split First Name

Steps

Copy column B and paste it into column C where you want to extract the first name from the full name.

Select column C.

Go to the Home tab in the ribbon and click Find & Select from the Editing group.

Select Replace from Find & Select drop-down menu.

A Find and Replace window will pop up. Place ‘ *’ (put a space then insert an asterisk sign) in the Find what field. This is a wildcard character(*) that can be used in place of any text. Place space and then a wildcard character. Leave Replace with field as blank. Click Replace All.

This will eventually replace all the text after the space with blank and just return the first name from the full name.

2.2 Split Last Name

Steps

Copy column B and paste it into column D where you want to extract the last name from the full name.

Select column D.

Go to the Home tab in the ribbon and select Find & Select from the Editing group.

Select Replace from Find & Select option.

A Find and Replace window will pop up. Place ‘* ’ (put an asterisk sign first then insert space) in the Find what field. This is a wildcard character that can be used in place of any text. Place a wildcard character(*) and then put space. Leave Replace with as blank. Click Replace All.