Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Sep 2004
    Location
    St. Louis, Missouri, USA
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    reverse last name, first name (Office 2003, Windows XP)

    Does anyone have a macro or something that can quickly reverse a last name, first name format where they are stored in one column (cell) into first name-space-last name (eliminating the comma, o f course). Should have been in two separate columns I realize, but it's too late now to worry about that.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: reverse last name, first name (Office 2003, Windows XP)

    A formula-based approach: say that your names are in column A, starting in A1. In cell B1, enter this formula:

    <code>=MID(A1,FIND(",",A1)+2,999)&" "&LEFT(A1,FIND(",",A1)-1)</code>

    and fill down as far as needed. You can now hide column A, or if you wish to remove the original data:
    - select the cells with the formulas in column B.
    - copy to the clipboard (Edit | Copy or Ctrl+C or the Copy button on the toolbar)
    - select Edit | Paste Special...
    - select the Values option and click OK.
    - you can now safely delete column A, or move the values from column B to column A.

    A macro-based approach: create the following macro:

    Sub SwitchFirstLast()
    Dim oCell As Range
    Dim intPos As Integer
    For Each oCell In Selection
    intPos = InStr(oCell, ",")
    If intPos > 0 Then
    oCell = Mid(oCell, intPos + 2) & " " & Left(oCell, intPos - 1)
    End If
    Next oCell
    Set oCell = Nothing
    End Sub

    Select the cells with the names.
    Select Tools | Macro | Macros...
    Select SwitchFirstLast and click Run.

  3. #3
    Star Lounger
    Join Date
    Sep 2004
    Location
    St. Louis, Missouri, USA
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: reverse last name, first name (Office 2003, Windows XP)

    Thanks, Hans, it worked beautifully.


    Lana

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: reverse last name, first name (Office 2003, Windows XP)

    If you want to separate the names into separate cells, that is actually easier than what Hans gave you.

    1- Make sure there are enough empty columns to the right of the column with the names to handle the name with the most parts.

    2- Select the column with the names.

    3- Select "Text To Columns" from the Data menu.

    4- In the first dialog box put a tick mark next to Delimited and then click the Next button.

    5- In the second dialog box put check marks next to Space and Comma. Click the Finish button.

    Your names should now be in separate columns.
    Legare Coleman

  5. #5
    Star Lounger
    Join Date
    Sep 2004
    Location
    St. Louis, Missouri, USA
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: reverse last name, first name (Office 2003, Windows XP)

    Hi Legare,

    You are right, - that method, too, worked quite smoothly. I can't thank you both enough for such quick replies - I was, as usual, in a hurry.

    Lana

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •