FIND or LEFT or MID to swap first name with last name?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Separate first name, middle name and last name 2
Formula to Split First and Last Names 2
Last Name 7
I need to swap last name, first name in a cell to first name last 3
Easily mailing list from Data File 3
parsing a full name out into Last, First, Middle 2
Separate First Name MI and Last Name into three Cells 3
First Name & Last Name 2

Back
Top