Thank you both. Peo's formula works great! I will try Jason's as well.
alice.
"Peo Sjoblom" wrote:
Assuming there is a space before the last name you can use this
=RIGHT(A1,LEN(A1)-SEARCH("^^",SUBSTITUTE(A1,"
","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))&",
"&SUBSTITUTE(A1,RIGHT(A1,LEN(A1)-SEARCH("^^",SUBSTITUTE(A1,"
","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))),"")
note that I added a comma this part &", "&, if you don't want that use
=RIGHT(A1,LEN(A1)-SEARCH("^^",SUBSTITUTE(A1,"
","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))&"
"&SUBSTITUTE(A1,RIGHT(A1,LEN(A1)-SEARCH("^^",SUBSTITUTE(A1,"
","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))),"")
it will fail for last name plus JR and II etc but those are probably quite
few and can be done with manually
Regards,
Peo Sjoblom
"Alice" wrote:
Not sure how the formula should read to swap first name with last name in a
sheet. All names were entered using First name, Middle initial and Last
name. Sometimes Middle name is used rather than initial. Not sure how to
designate position of Last name. Thanks.
--
Alice.
|