# Extracting middle names and last names

This is a discussion on Extracting middle names and last names within the Excel Questions forums, part of the Question Forums category; Hi, I have seen&#65279; many different ways to extract middle names and last names. Some are really confusing to figure ...

1. ## Extracting middle names and last names

Hi,

I have seen﻿ many different ways to extract middle names and last names. Some are really confusing to figure out.

Some people use, find and others use search to find a pattern occurring.

What is the best and easiest way/formula to use to extract middle and last names. I guess the first name is pretty easy as everyone seems to extract the first name using the same formula. What if there is 1,2 or more middle names...

i,e

STEVEN JOHN PAUL GERRARD
MICHAEL JOHN H ANTHONY SMITH
SHAUN H NORMAN
ROGER CLARK

What is the best method/easiest formula to extract any middle names and last names. Can you please give examples of different formulas.

What's the difference between the search and find?

gracias

3. ## Re: Extracting middle names and last names

hi,

the trick here is to find the position of the spaces in the name. you can use the search function to do. =search(findtext,within text,[start num]

after you find the position of the first space, then you find the position of the second space, by using the [start num] part of the function,

eg

myen dushern pather
search(" ",A1) will give me and answer = 5
then i use search(" ",A1,6) will give me = 13
i would also use len function to find the entire length of the name.
to get the middle name you could use =mid(A1,6,(13-5) will give dushern
mid function allows to retrive text from a cell from a specific position amongst the text in the cell for a specfic number of characters. its better to under stand the solution so that you can use it in other cases.

later guy
myen pather

4. ## Re: Extracting middle names and last names

mahmed1,

Sheet1

 A B C D 1 Full Name First Middle Last 2 STEVEN JOHN PAUL GERRARD STEVEN JOHN PAUL GERRARD 3 MICHAEL JOHN H ANTHONY SMITH MICHAEL JOHN H ANTHONY SMITH 4 SHAUN H NORMAN SHAUN H NORMAN 5 ROGER CLARK ROGER CLARK 6

 Cell Formula B2 =LEFT(A2,FIND(" ",A2,1)-1) C2 =IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=1,"",MID(A2,FIND(" ",A2,1)+1,FIND(MID(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,255),A2,1)-2-FIND(" ",A2,1))) D2 =MID(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,255)

Excel tables to the web >> Excel Jeanie HTML 4

The formula in cell B2 copied down:
=LEFT(A2,FIND(" ",A2,1)-1)

The formula in cell C2 copied down:
=IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=1,"",MID(A2,FIND(" ",A2,1)+1,FIND(MID(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,255),A2,1)-2-FIND(" ",A2,1)))

The formula in cell D2 copied down:
=MID(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,255)

5. ## Re: Extracting middle names and last names

hey thanks hiker. This is a handy reference. Ill use it in the future.

6. ## Re: Extracting middle names and last names

Originally Posted by hiker95
mahmed1,

Sheet1

 A B C D 1 Full Name First Middle Last 2 STEVEN JOHN PAUL GERRARD STEVEN JOHN PAUL GERRARD 3 MICHAEL JOHN H ANTHONY SMITH MICHAEL JOHN H ANTHONY SMITH 4 SHAUN H NORMAN SHAUN H NORMAN 5 ROGER CLARK ROGER CLARK 6

 Cell Formula B2 =LEFT(A2,FIND(" ",A2,1)-1) C2 =IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=1,"",MID(A2,FIND(" ",A2,1)+1,FIND(MID(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,255),A2,1)-2-FIND(" ",A2,1))) D2 =MID(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,255)

Excel tables to the web >> Excel Jeanie HTML 4

The formula in cell B2 copied down:
=LEFT(A2,FIND(" ",A2,1)-1)

The formula in cell C2 copied down:
=IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=1,"",MID(A2,FIND(" ",A2,1)+1,FIND(MID(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,255),A2,1)-2-FIND(" ",A2,1)))

The formula in cell D2 copied down:
=MID(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,255)

Thank you so much for this formula

Can you please explain how the formula works as i am a fairly new learner in Excel.

Much appreciated

7. ## Re: Extracting middle names and last names

mahmed1,

Can you please explain how the formula works as i am a fairly new learner in Excel.
The formula in column B:
=LEFT(A2,FIND(" ",A2,1)-1)

Finds the first "space", and returns the LEFT part of the string in cell A2, for the the number of characters up to, and not including the "space".

The formula in column D:
=MID(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,255)

was a formula from another post on MrExcel. I think that it finds the last "space" character in cell A2, and returns the MID part of the string from the next character past the last "space" for the LEN (length) of the string minus the last character position of the last "space".

The formula in column C:
=IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=1,"",MID(A2,FIND(" ",A2,1)+1,FIND(MID(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,255),A2,1)-2-FIND(" ",A2,1)))

First counts how many "space" characters there are in the string, and if these is only "1 space" character, then there is no middle name, and returns a blank cell.

If there are more than "1 space" characters in cell A2, then we use MID to return the string from the starting position + 1 of the first "space" character, to the "last space" character position - 1.

8. ## Re: Extracting middle names and last names

mahmed1,

In case you are interested in another reference...

http://www.ozgrid.com/Excel/TextFormulas.htm
or
http://www.cpearson.com/excel/FirstLast.htm

9. ## Re: Extracting middle names and last names

Thank you all for your help

10. ## Re: Extracting middle names and last names

Thank you VERY much. This is the cleanest and clearest version of extracting names...and very helpful for me.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•