Table of Contents

How Do I Get Someone’s Middle Name From a Cell With Their Full Name In Excel?

To extract the middle name from a full name in Excel, you can use a combination of functions such as LEFT, RIGHT, and LEN, along with the FIND function. Here are the step-by-step instructions:

Assuming the full name is in cell A1, follow these steps:

1. Use the following formula to extract the middle name:

=MID(A1, FIND(” “, A1) + 1, FIND(” “, A1, FIND(” “, A1) + 1) – FIND(” “, A1) – 1)

This formula uses the MID function to extract the middle name from the full name. The FIND function is used to locate the positions of the spaces in the full name, and the result is the middle name.

2. Press Enter to get the middle name extracted from the full name.

After following these steps, the middle name from the full name in cell A1 will be extracted using the formula provided.

How to Get a Middle Name From a Full Name in Excel According Quora

This solution was proposed by Guven Cetin

Another possible solution is as highlighted by Given

Hi. Please try:

=IFERROR(MID(MID(A2,1,FIND(“~”,SUBSTITUTE(A2,” “,”~”,LEN(A2)-LEN(SUBSTITUTE(A2,” “,””))))-1),FIND(” “,MID(A2,1,FIND(“~”,SUBSTITUTE(A2,” “,”~”,LEN(A2)-LEN(SUBSTITUTE(A2,” “,””))))-1))+1,100),”-“

— Assuming your names are in Col A.

The formula is kind of long, because I tried to foolproof it. As you know some people have more than one middle names, and some people have none. So a direct approach to get only the second or second to last word in cell could potentially lead to faulty results.

What the formula does:

  1. Removes the last word/last name from text, and then
  2. Removes the first word/first name from the remaining text, and finally
  3. If no text is left, meaning if there is no middle name, give you the dash (-) character.


Separating Middle Name from Full Names

This will only fail if the dual surname is not hyphenated as pointed. In this case neither Excel nor a person will have no way to predict what is a surname and what is a middle name, so that was the only case I was unable to foolproof the formula for.

See below:

Separating Middle Name from Full Names

The functions used in the formula:

  • SUBSTITUTE : Finds a specified text in the referenced data and replaces it with another specified text. Example:
    • =SUBSTITUTE(A2,”a”,”b”) > “Bbrry Steven Hbrrison”
  • LEN : Determines the length of referenced data. Example:
    • =LEN(B2) > 6
    • =LEN(B3) > 10
  • FIND : Gives the numerical location of a specified text where it’s first found in the referenced data. Example:
    • =FIND(“S”,A2) > 7
    • =FIND(“ “,A2) > 6
  • MID : Returns a specified length of text starting from a specified location in the referenced data. Example:
    • =MID(A2,3,9) > “rry Steve” (starting from the 3rd character in A2, returns the following 9 characters)
  • IFERROR : If the written formula returns an error, gives you another specified text or value. Examples:
    • =IFERROR(a*0,9) > 9 __ (text and number is multiplied (a*0) to generate error)
    • =IFERROR(a*0,”text”) > text

Leave A Comment

Excel meets AI – Boost your productivity like never before!

At Formulas HQ, we’ve harnessed the brilliance of AI to turbocharge your Spreadsheet mastery. Say goodbye to the days of grappling with complex formulas, VBA code, and scripts. We’re here to make your work smarter, not harder.

Related Articles

The Latest on Formulas HQ Blog