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



"Harlan Grove" wrote:

Dave F wrote...
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.