Extracting middle names and last names

[mahmed1]

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...<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
i,e <o></o>
<o> </o>
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. <o></o>
<o> </o>
What's the difference between the search and find?

gracias <o></o>
<o> </o>

 

[Akashwani]

Hi,

This may help...

http://www.youtube.com/user/ExcelIsFun#p/search/4/p1jfWxkEFTY

Ak

 

[MyenPather]

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

 

[hiker95]

mahmed1,

Excel Workbook
	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
Sheet1

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)

 

[momsyhanny]

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

 

[mahmed1]

mahmed1,

Excel Workbook
	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
Sheet1

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

 

[hiker95]

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.

 

[jeffreybrown]

mahmed1,

In case you are interested in another reference...

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

 

[mahmed1]

Thank you all for your help

 

[maryinred]

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

I appreciate your sharing it.