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.