How to Separate First Middle and Last Name in Excel Using Formula

This article will show how to separate the first name, middle name, and last name with the help of an Excel formula. To get a quick view on this, have a look below.

Separate First Name Middle Name and Last Name in Excel Using Formula: Overview


Download Practice Workbook

Click the following link and download the practice workbook for free.


3 Ways to Separate First Name Middle Name or Last Name Using Formula

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. So, here we will show the use of different individual formulas to separate the parts of the whole name.

In the following dataset, we have a list of real names of several prominent actors.

Sample Dataset of Famous Actors' Names

We will use Excel formulas to extract the first, middle, and last names from their full names (Column B).


1. Separate the First Name with Excel LEFT and SEARCH Functions

You can easily extract the first name using just the LEFT function alone or combining it with the SEARCH function.

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.

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.


1.1 Use LEFT-SEARCH Formula

Execute the following steps to get the first names separated.

Steps:

  • Firstly, copy the following formula and enter it 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.

Use LEFT and SEARCH Functions to Separate First Name in Excel

Note: 

You can also use the FIND function, instead of the SEARCH function. The difference between them is that SEARCH is not case-sensitive, hence more flexible, while FIND is case-sensitive. But both the functions will work fine in this case.


1.2 Use the LEFT Function Only

The steps to execute are as follows.

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 (W i l l i a m).
  •  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 cells and repeat all the previous steps.

The final output will be as in the following image. 👇 I have shown the corresponding formulas too.

Use LEFT Function to Separate First Name in Excel

Read More: How to Split Names into Three Columns in Excel (3 Methods)


2. Separate the Last Name (With or without Middle Names)

There may be types of names. Names with a middle name, and names without a middle name! To separate the last name from a full name, we have to consider whether it has a middle name in it or not. Depending on this, the formulas will vary.

2.1 When There Are Middle Names

We will apply a formula using the RIGHT, LEN, FIND and SUBSTITUTE functions in the second method.  Just apply the steps below.

Steps: 

  • Type or copy the following formula in cell C5.

=RIGHT(A2,LEN(A2)-FIND("^",SUBSTITUTE(A2," ","^",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

  • Hit the ENTER button and pull the Fill Handle icon all the way.

Combine RIGHT, SEARCH, SUBSTITUTE, and LEN Functions to Separate the Last Name in Excel

The final result is shown below. 👇

Note:

You can also use the SEARCH function instead of the FIND function.

🔎 How Does the Formula Work?

  • SUBSTITUTE(B5,” “,””)
    The SUBSTITUTE function replaces the spaces “ “ from the text string of cell B5 with the empty string “”.
    Result: “WilliamBradleyPitt”.
  • LEN(SUBSTITUTE(B5,” “,””))
    The LEN function returns the length of “WilliamBradleyPitt”.
    Result: 18.
  • LEN(B5)-LEN(SUBSTITUTE(B5,” “,””))
    LEN(B5) equals the number of characters in a text string in B5 including spaces, while LEN(SUBSTITUTE(B5,” “,””)) returns the length of the text string in cell B5 excluding spaces. So their difference equals the number of spaces in cell B5 text string.
    Result: 2.
  • SUBSTITUTE(B5,” “,”^”,LEN(B5)-LEN(SUBSTITUTE(B5,” “,””)))))
    Here, the output of LEN(B5)-LEN(SUBSTITUTE(B5,” “,””))=2 which works as the 4th argument of this SUBSTITUTE function. So it denotes that the SUBSTITUTE function will replace the 2nd space character “ “ in cell B5 text string.
    Result: “William Bradley^Pitt”.
  • FIND(“^”,SUBSTITUTE(B5,” “,”^”,LEN(B5)-LEN(SUBSTITUTE(B5,” “,””))))
    The output of SUBSTITUTE(B5,” “,”^”,LEN(B5)-LEN(SUBSTITUTE(B5,” “,””))) part is “William Bradley^Pitt”. So the FIND function finds the position of the “^” character from the text string “William Bradley^Pitt” from the left corner.
    Result: 16.
  • LEN(B5)-FIND(“^”,SUBSTITUTE(B5,” “,”^”,LEN(B5)-LEN(SUBSTITUTE(B5,” “,””))))
    Subtracting the output of FIND(“^”,SUBSTITUTE(B5,” “,”^”,LEN(B5)-LEN(SUBSTITUTE(B5,” “,””)))) from LEN(B5) will result in the number of characters in the last name.
    Result: 4.
  • =RIGHT(B5,LEN(B5)-FIND(“^”,SUBSTITUTE(B5,” “,”^”,LEN(B5)-LEN(SUBSTITUTE(B5,” “,””)))))
    LEN(B5)-FIND(“^”,SUBSTITUTE(B5,” “,”^”,LEN(B5)-LEN(SUBSTITUTE(B5,” “,””)))) returns 4, hence this whole formula has become actually this: =RIGHT(B5,4).
    Result: Pitt.

2.2 When There Is No Middle Name

If there is no middle name in the full names, then our job is easier. To separate the last names in such cases, we have to identify the position of the space character in the full name from left to right, subtract the result from the length of the full name text string, then use this 2nd result as the num_chars argument of the RIGHT function.

Apply the following easy steps to do this.

Steps: 

  • Write 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.

Separate Last Name with a Simpler Formula If There Is No Middle Name

Here is the result. 👇

🔎 Formula Breakdown

  • SEARCH(” “,B5)
    The SEARCH function returns the position of the space character (” “) in cell B5 from the left.
    Result: 5.
  • LEN(B5)-SEARCH(” “,B5)
    This subtraction returns the number of characters in the second part (the last name) of the text string (the full name).
    Result: 4.
  • RIGHT(B5,LEN(B5)-SEARCH(” “,B5))
    The LEN(B5)-SEARCH(” “,B5) part returns the number of characters in the last name. So, the RIGHT function will return 4 characters from the text string in cell B5, i.e. the last name.
    Result: Pitt.

Read More: How to Split Names with Comma in Excel (3 Suitable Ways)


3. Separate Middle Names (Single or Multiple)

We can have single or multiple middle names. In each case, we have to use separate Excel formulas. We have shown both of them below.

3.1 Separate Single Middle Name with MID-SEARCH Formula

To separate a single middle name, we can use a formula with the MID function combined with multiple SEARCH functions. The MID function returns a given number of characters from the middle of a specified text string, starting from a given position.

Syntax of MID Function:

=MID(text, start_num, num_chars)

Now, follow the steps below.

Steps:

  • First of all, type 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 till cell C9 to copy the formula in all cells.

MID-SEARCH Formula to Extract Middle Name

See the picture below which is showing the middle names are separated perfectly. 👇

Note:

If some of the full names have the first and last names only, the formula will have an IFERROR function in the starting.

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

🔎 Formula Breakdown

The MID function used in this formula has three required arguments.

» B5 refers to the text argument.
» SEARCH(” “,B5)+1 refers the start_num argument.
» SEARCH(” “,B5,SEARCH(” “,B5)+1)-(SEARCH(” “,B5)+1) returns the num_chars argument for MID function.

  • SEARCH(” “, B5) + 1
    The SEARCH function searches for a space character (” “) in the text string in cell B5 and returns the position of space from the left corner of the string. Adding 1 with its output gives the position where the middle name starts.
    Result: 9.
  • SEARCH(” “, B5, SEARCH(” “, B5)+1) – (SEARCH(” “, B5)+1)
    In this part, there are three SEARCH functions.  SEARCH(” “, B5)+1) part inside the first SEARCH function returns the starting position for it from which to count the position of the “ “ character. Thus SEARCH(” “, B5, SEARCH(” “, B5)+1) returns the position of the second space character in the text string. Subtracting the output of (SEARCH(” “, B5)+1) from this will return the num_chars argument for the MID function.
    Result: 7.
  • MID(B5, SEARCH(” “, B5) + 1, SEARCH(” “, B5, SEARCH(” “, B5)+1) – (SEARCH(” “, B5)+1))
    Finally, the MID function returns the middle name.
    Result: “Bradley”.

3.2 Separate Multiple Middle Names

If you have multiple middle names, you still can separate them from the full names. Just like in the earlier formula, we will use the MID function. With that, we will combine TRIM and LEN functions too.

Generic Formula to Separate Multiple Middle Names:

=TRIM(MID(name,LEN(first)+1,LEN(name)-LEN(first&last)))

Steps:

  • First of all, you have to separate the first and last names into two separate columns C and D. We have discussed this in 1.1 and 2.

  • 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. 👇

🔎 Formula Breakdown

  • LEN(C5&D5)
    This returns the total number of characters in the first and the last names.
    Result: 10.
  • LEN(B5)-LEN(C5&D5)
    This returns the number of characters left in the text string in cell B5 except the first and the last names, i.e. number of characters in the middle names plus the leading and trailing spaces.
    Result: 13.
  • LEN(C5)+1
    This portion returns the length of the first name plus 1, i.e. where to start returning the middle name from cell B5 by the MID function.
    Result: 6.
  • MID(B5,LEN(C5)+1,LEN(B5)-LEN(C5&D5))
    This becomes MID(B5,6,13) and returns the 13 characters in cell B5 starting from the 6th character from the left.
    Result: ” E. Hoffmann “
  • =TRIM(” E. Hoffmann “)
    Finally, the TRIM function omits the extra spaces except for the spaces between two middle names.
    Result: “E. Hoffmann”.

More Ways to Separate First Middle and Last Name in Excel

So far, we have learned several formulas with which we can separate first, middle, or last names in Excel. Now in this section, we will learn some more methods without Excel formulas.

1. Split Full Names with the Help of Text to Columns Feature

Excel Text to Columns wizard enables us to separate the first, middle, or last names simultaneously. Just follow the simple steps below.

Steps:

  • Select the range of cells B5:B9 that contain the full names.
  • Go to the Data tab >> the Data Tools group >> press the Text to Columns button.

Split Full Names with the Help of Text to Columns Feature

  • The following window will pop up. Press the Delimited button and then press Next>.

Split Full Names with the Help of Text to Columns Feature

  • Mark the Space checkbox and press Next> again.

Split Full Names with the Help of Text to Columns Feature

  • Select Column data format as General.
  • Select the Destination cell C5, where the first output will take place.
  • Press Finish.

Split Full Names with the Help of Text to Columns Feature

  • If the following pop-up appears, press OK.

The following picture shows the separated names. 👇

Read More: How to Split Names in Excel (5 Effective Methods)


2. Separate the First, Middle, or the Last Name Using Flash Fill in Excel 2013, 2016, 2019 & 365

Excel Flash Fill is an awesome feature that can sense patterns in cells and autofill the next cells according to the pattern. If you are using Excel 2013, 2016, 2019, or 365, you can use this feature.

The following steps will show, how to extract/remove the first, middle, or last names using Excel Flash Fill.

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.

Separate the First, Middle, or the Last Name Using Flash Fill in Excel 2013, 2016, 2019 & 365

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: Excel VBA: Split First Name and Last Name (3 Practical Examples)


3. Separate Names Using Find and Replace

The Find and Replace tool is another Excel feature that has vast functionality. It allows the use of Wildcards in it. We will use the asterisk (*) wildcard in Find and Replace in this section to extract the first, middle, and last names.

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 (*).
  • In Replace with: box, type nothing. Leave it blank.
  • Press the Replace All button.

Separate Names Using Find and Replace

The following image shows that this method works perfectly. 👇

Separate Last Names:

  • Copy the full names in a separate column and select them.
  • Press CTRL+H.
  • To separate the last names, type an asterisk (8) followed by a space in the Find what: box. Leave the Replace with: box empty.
  • Now, press Replace all button.

The following image shows the results. 👇


3.2 Remove Middle Name from the Full Name

To remove the middle names from the full name, the working procedure is just the same as in 3.1. But you have to 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. Then press Replace All button.

Read More: How to Split Names Using Formula in Excel (4 Easy Methods)


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 sense the order.

Conclusion

We have discussed 3 formula examples to separate first, middle, and last names in Excel with the pros and cons of each. If you have any questions, please let us know in the comment section. To read more Excel-related articles, visit our site ExcelDemy.


Related Articles

Masum Mahdy

Masum Mahdy

Hi there! I am Mahdy, a graduate of Naval Architecture from BUET, currently working as an Excel & VBA Content Developer in ExcelDemy. You are gonna find my published articles on MS Excel and other topics of my interest here in ExcelDemy's blog. You are most welcome to my profile!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo