Easy one...
Fails on names like:
Martin St Louis (real name of a hockey player)
Oscar De La Hoya (real name of a boxer)
--
Biff
Microsoft Excel MVP
"Teethless mama" wrote in message
...
=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=1,MID(A1,FIND(" ",A1)+1,99)&"
"&LEFT(A1,FIND(" ",A1)-1),MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,99)&"
"&LEFT(A1,FIND(" ",A1))&MID(A1,FIND(" ",A1)+1,1))
Note: The above formula will work with or without middle name as along as
middle name contain a single character.
"NWO" wrote:
Ok, I have a list of names in the fomrat John A Doe, Joan Bennett, etc
(no
commas, some have middle initial, some don't). How do I get Doe John A,
and
Bennett Joan (last name first name middle initial - commas OK) I tried
the
Pearson example, but it had commas and the last name was first, so it
didn't
work for my problem.
Thank you.
NWO Mark
|