FIND or LEFT or MID to swap first name with last name?
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. |
One way:
=RIGHT(A1,MATCH(" ",MID(A1,1+LEN(A1)-ROW(INDIRECT("1:"&LEN (A1))),1),0)-1)&" "&LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)) Press ctrl/shift/enter, not just enter. HTH Jason Atlanta, GA -----Original Message----- 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. . |
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. |
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. |
All times are GMT +1. The time now is 03:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com