Reverse names in Excel using VBA

Posted on 2011-10-24
Last Modified: 2012-05-12
I have some Excel files that have names listed either as last name, first name or first name last name in a cell.  How can I easily switch these around either using visual basic or a formula?

For example if cell A2 is Doe, John how can I make it John Doe?

Alternatively if I have A2 as John Doe how can I convert it to Doe, John?

I have a few very large spreadsheets that would need this done on column A for example.
Question by:snailcat
    LVL 39

    Accepted Solution

    Formula would be

    =left(a2, find(",",A2)-1)  & right(a2,len(a2)-find(",",a2))
    for the first case


    Assuming no middle names, middle initials or composed first or last names with spaces in the middle

    =trim(right(a2,len(a2)-find(" ",a2))) &", " & left(a2, find(" ",A2)-1)

    LVL 39

    Expert Comment

    My bad on the first one, it doesn't change the order:

    =RIGHT(A2,LEN(A2)-FIND(",",A2)-1) & " " & LEFT(A2, FIND(",",A2)-1)

    LVL 43

    Assisted Solution

    by:Saqib Husain, Syed
    Try this formula

    =RIGHT(A1,LEN(A1)-FIND(" ",A1))&IF(ISERR(FIND(",",A1)),",","")&" "&LEFT(A1,FIND(" ",A1)-2+ISERR(FIND(",",A1)))
    LVL 50

    Assisted Solution

    by:barry houdini
    Doe, John to John Doe

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

    regards, barry

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now