formula pulling first name and middle name in same column

**johnmerlino** · 10-29-2010, 10:06 AM

You'd be suprised that there's nothing on net addressing this specific case:
Amador,Felipe Jose
Baptiste,Roger

although you would think there should be.
Here there is no space between last and first.
=TRIM(A2)
Getting last is straight forward:
=LEFT(F2, FIND(",",F2)-1)

Pulling the first and middle initial, if exists, in separate columns is not so straight forward:
=MID(F2,FIND(",",F2,FIND(" ",F2)+1)+1,FIND(" ",F2))
That gives VALUE error

**johnmerlino** · 10-29-2010, 10:13 AM

This worked for first name:
=MID(F2,FIND(",",F2)+1,FIND(" ",F2)-FIND(",",F2)-1)

**johnmerlino** · 10-29-2010, 10:15 AM

And this worked for middle initial:
=MID(F2,FIND(" ",F2&" ")+1,255)

**scottylad2** · 10-29-2010, 10:16 AM

THe Text to columns function will separate your names no problem. Make sure you tick the delimiter as both a comma and a space

**Marcol** · 10-29-2010, 10:16 AM

Is this a statement, or a question?

If it's a question, what results are you expecting?

**johnmerlino** · 10-29-2010, 10:22 AM

It was initially a question, but then I began figuring out myself, but then then scottylad mentioned text to column can take care of it and he's right. It worked.