Extracting Middle Name/Initial when name fields look like First Mid Last

**kmn** · 04-30-2011, 12:35 PM

I've uncovered excellent text conversion formulas to extract both the first name and last name from an 800+ column of names that look like:
Davy J Jones
Marcus Welby
Anthony Lazy Denosso
Janis Joplin
Mark T Seager
Mary Jean Post
Some fields have middle names an/or initials, but not all. I cannot find a formula to extract the middle name and/or initial, that also will result in a blank field if there is no middle name/initial.

See attached test file....

Can anyone help?

**arthurbr** · 04-30-2011, 01:00 PM

You can use the following for the middle name

Please Login or Register  to view this content.

**Marcol** · 04-30-2011, 01:00 PM

With your formulae in B2, D2, and E2

Try this in C2

Please Login or Register  to view this content.

Hope this helps

**daddylonglegs** · 04-30-2011, 01:03 PM

That woulldn't work for a name like "John Jo Johnson" though

Try

=IF(COUNTIF(A2,"* * *"),TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",49)),49,49)),"")

**Marcol** · 04-30-2011, 01:29 PM

True DLL

How about then in C2

Please Login or Register  to view this content.

**martindwilson** · 04-30-2011, 02:03 PM

why not just wrap what you have already in iferror?

**david.f.mayer** · 05-23-2013, 04:32 PM

To convert First Middle Last to Last First Middle:

=IF(RC[-1] > " ",TRIM(RIGHT(SUBSTITUTE(RC[-1]," ",REPT(" ",255)),255)) & " " & LEFT(RC[-1],FIND(" ",RC[-1])-1) & " " & IF(LEN(RC[-1])-LEN(SUBSTITUTE(RC[-1]," ",""))>1,TRIM(SUBSTITUTE(SUBSTITUTE(RC[-1], LEFT(RC[-1],FIND(" ",RC[-1])-1),""), TRIM(RIGHT(SUBSTITUTE(RC[-1]," ",REPT(" ",255)),255)),"")),""),"")

**nemo74** · 05-23-2013, 05:20 PM

Originally Posted by david.f.mayer

To convert First Middle Last to Last First Middle:

=IF(RC[-1] > " ",TRIM(RIGHT(SUBSTITUTE(RC[-1]," ",REPT(" ",255)),255)) & " " & LEFT(RC[-1],FIND(" ",RC[-1])-1) & " " & IF(LEN(RC[-1])-LEN(SUBSTITUTE(RC[-1]," ",""))>1,TRIM(SUBSTITUTE(SUBSTITUTE(RC[-1], LEFT(RC[-1],FIND(" ",RC[-1])-1),""), TRIM(RIGHT(SUBSTITUTE(RC[-1]," ",REPT(" ",255)),255)),"")),""),"")

Ok,
that is just crazy looking right there.