Excel 2007 Guru needed! How to swap (first name) <>(lastname)?

I have a bunch of names in the following format. Each name is in it's own cell

All of them have {First name> S- > Last name}

Example:

Jane S-Doe

I would like it to be: {S- > Last name, > First name}

Example:

S-Doe, Jane

Is there a way to do this automatically with a formula or macro?

Update:

LoverofWine - Thank you so much! That solution is almost perfect! I just need a space after the comma. Is there a way to add it in the formula?

2 Answers

Relevance
  • DotCom
    Lv 4
    1 decade ago
    Favorite Answer

    Hello pablobui,

    Good posted answer to your qustion. I would like to offer an alternative for you testing:

    =MID(A1&", "&A1,FIND("^^",SUBSTITUTE(A1," ","^^",3))+1,LEN(A1)+1)

    Problem using the above formula, would be a question such as:

    "Not every row in the column have multiple first names such as Jane and John Doe. Some in the column are singles such as Jane Doe."

    So to help rectify this problem try the following formula:

    =MID(A1&", "&A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,LEN(A1)+1)

    hope this helps,

    dtcom

    • Login to reply the answers
  • 1 decade ago

    Maybe, or at least you can do most. Try a formula like

    =CONCATENATE(RIGHT(A1, LEN(A1) - FIND(" ",A1)), ",",LEFT(A1, FIND(" ",A1) -1))

    That will work for any name that has a single space in it.

    • Login to reply the answers
Still have questions? Get your answers by asking now.