parse text string

[Dave F]

Let's say I have a bunch of names, such as:

John Doe
Jane Doe
Jim Doe

and I want to re-arrange them as
Doe, John
Doe, Jane
Doe, Jim

I can figure out that a formula to do this is: =MID(A2,FIND("
",A2,1)+1,LEN(A2)) & ", " & LEFT(A2,FIND(" ",A2)) etc.

However, if the name is John M Doe the above returns M Doe, John which
isn't too helpful.

So is there a single formula I can use to parse names, whether they
are First Last or First Middle Initial Last to return Last First or
Last First Middle Initial?

I'm also open to using VBA if that is a better solution.

Thanks,

Dave

 

[T. Valko]

Assuming that *all* names are either format:

John Doe
John M Doe

=MID(A2&", "&A2,FIND(" ",A2)+IF(LEN(A2)-LEN(SUBSTITUTE(A2,"
",""))>1,3,1),LEN(A2)+1)

Returns:

Doe, John
Doe, John M

Biff

 

[T. Valko]

Just in case the method you're using to view these groups causes a line wrap
problem:

=MID(A2&", "&A2,FIND(" ",A2)+IF(LEN(A2)-
LEN(SUBSTITUTE(A2," ",""))>1,3,1),
LEN(A2)+1)

Biff

 

[Harlan Grove]

Let's say I have a bunch of names, such as: ....
However, if the name is John M Doe the above returns M Doe, John which
isn't too helpful.

So is there a single formula I can use to parse names, whether they
are First Last or First Middle Initial Last to return Last First or
Last First Middle Initial?

....

So there'd either be just two or three words in each name and only the
last word would be the last name? If so,

=IF(LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))=1,
MID(A1,FIND(" ",A1)+1,256)&", "&LEFT(A1,FIND(" ",A1)-1),
MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,256)&", "&
LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)))

But that won't work for multiple word surnames like de la Hoya or
Lloyd George. There's no simple formula that works for all names.

 

[JLatham]

Not to mention the other oddities such as
John Jones III
John Brown, Sr.
or names with an honorific as
John Smith, PhD