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:
- Removes the last word/last name from text, and then
- Removes the first word/first name from the remaining text, and finally
- If no text is left, meaning if there is no middle name, give you the dash (-) character.
Example:
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:
—
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
