Extract first middle & last names

[Burrgogi]

This is actually a continuation of this thread. I started a new one for two main reasons. Zcraig's question has already been answered and Papi's asked a question in the middle of that thread. His situation is slightly different. Papi, I've been thinking about your question for a couple of days and as powerful as Excel is... I had a feeling there was a way to do it w/out macros.

I have no professional knowledge of Excel - I actually learned how to do this watching a You Tube video. Assuming that you have a list of names that start in A2, and assuming that you have row labels in row 1, "First Name", "M.I", "Last Name", etc.

Copy & paste the following into Cell B2
=LEFT(A2,SEARCH(" ",A2)-1)

Copy & paste the following into Cell C2 *
=IFERROR(MID(A2,LEN(B2)+2,LEN(A2)-LEN(B2)-LEN(D2)-2),"")

Copy & paste the following into Cell D2
=REPLACE(A2,1,SEARCH("^",SUBSTITUTE(A2," ","^",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))),"")

* Note that te IFERROR function is available only in 2007 & 2010 versions. If you have an earlier version, post back and I can offer a slightly different formula to extract the middle name.

 

[pboltonchina]

Why not just use Text to Columns, Delimited, with Space as the delimiter?

 

[Burrgogi]

Why not just use Text to Columns, Delimited, with Space as the delimiter?

The answer is quite obvious isn't it? Look at row 4 with the example of John Smith.

If you really had a sample of only 6 names to deal with (as in the screenshot I provided above) then you could manually edit row 4 yourself. But what if you had a list of 100 names, with some of the names not having a middle initial. Rather tedious to manually search & edit every such occurrence don't you think?

 

[hawk_penn]

Can you post the 2003 middle name please

 

[Burrgogi]

For the middle name extraction, use the following formula if you're using '03 or earlier versions.
I no longer use the 2003 version - so you'll just have to test it out and see if it works or not.

=IF(ISERROR(MID(A2,LEN(B2)+2,LEN(A2)-LEN(B2)-LEN(D2)-2),"",MID(A2,LEN(B2)+2,LEN(A2)-LEN(B2)-LEN(D2)-2))

 

[Burrgogi]

I missed a few brackets. Formula has been revised.

=IF(ISERROR(MID(A2,LEN(B2)+2,LEN(A2)-LEN(B2)-LEN(D2)-2)),"",(MID(A2,LEN(B2)+2,LEN(A2)-LEN(B2)-LEN(D2)-2)))

 

[Sandeep Warrier]

Another way

Excel Workbook
	A	B	C	D
1	Full Name	First Name	Middle Name	Last Name
2	David G. Roberts	David	G.	Roberts
3	Eileen Sue Smith-Jones	Eileen	Sue	Smith-Jones
4	John Smith	John		Smith
5	Robert James Harris	Robert	James	Harris
6	Mary Jo Robbins	Mary	Jo	Robbins
Sheet1

Excel 2003

Cell Formulas
Range	Formula
B2	=LEFT(A2,FIND(" ",A2)-1)
C2	=IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))>1,TRIM(SUBSTITUTE(SUBSTITUTE(A2,B2,""),D2,"")),"")
D2	=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",255)),255))

 

[kdigit0l]

I found this information very useful, thanks!