Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. . |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I compare data in two worksheets to find matching cells? | Excel Discussion (Misc queries) | |||
Excel has a "Find Next" command but no "Find Previous" command. | Excel Discussion (Misc queries) | |||
Find & Replace results to display specified chosen fields | Excel Discussion (Misc queries) | |||
Find Fn from right? | Excel Worksheet Functions | |||
VB Find and Replace | Excel Worksheet Functions |