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

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

Jason Morin

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
 
G

Guest

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
Last Name 7
I need to swap last name, first name in a cell to first name last 3
parsing a full name out into Last, First, Middle 2
Formula to Split First and Last Names 2
Separate First Name MI and Last Name into three Cells 3
Seperating First and Last name from combined 3
extracting last name 4

Top