Results 1 to 8 of 8

How to reverse last name/first name seperated by a comma

This is a discussion on How to reverse last name/first name seperated by a comma within the Excel Questions forums, part of the Question Forums category; Anyone know how I can either reverse last name/first name in a column or put the first name in one ...

  1. #1
    New Member
    Join Date
    Nov 2006
    Posts
    8

    Default How to reverse last name/first name seperated by a comma

    Anyone know how I can either reverse last name/first name in a column or put the first name in one column and the second name in another? Currently, the names are: last name, first name in one column. I need to do a merge in InDesign for table tents.
    Thanks.

  2. #2
    Board Regular NBVC's Avatar
    Join Date
    Aug 2005
    Location
    Ontario
    Posts
    5,827

    Default

    Select the column and go to Data|Text to Columns....select Delimited and on the next screen select comma and select space.

    Click ok.
    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Microsoft MVP - Excel



  3. #3
    Board Regular ChrisUK's Avatar
    Join Date
    Sep 2002
    Location
    England, The Lake District
    Posts
    627

    Default

    Hi there and welcome to the board!

    I think we need some more information here first. Exactly how are the names stored ?

    FirstName#comma#SecondName ?

    If so use this:

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

    Hope this helps

    Chris

  4. #4
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,245

    Default

    Welcome to the Board!

    Here are some formula methods:

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book1___Running: 11.0 : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    Last, FirstFirstLastReversed
    2
    Floyd, PinkPinkFloydPink Floyd
    Sheet1

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE A JavaScript ERROR WILL OCCUR.


    You can also use Data-->Text to Columns-->Delimited with Comma as the Delimiter.

    Hope that helps,

    Smitty
    Smitty

    Every once in a while, there's a sudden gust of gravity...

    Check out my new book at the Mr. Excel Bookstore!

    Mr. Excel HTML Maker - Post a shot of your sheet

  5. #5
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,677

    Default

    Maybe:

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book1___Running: xl2002 XP : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    smith,*johnjohn*smith**
    Sheet1*

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
    Richard Schollar

    Using xl2013

  6. #6
    MrExcel MVP erik.van.geit's Avatar
    Join Date
    Feb 2003
    Location
    Belgium 3272 Testelt
    Posts
    17,765

    Default

    Hi, revwren
    Welcome to the Board !!!!!

    for a formulapproach, try this
    =MID(A1,SEARCH(",",A1)+1,999) &","& LEFT(A1,SEARCH(",",A1)-1)

    kind regards,
    Erik

  7. #7
    MrExcel MVP erik.van.geit's Avatar
    Join Date
    Feb 2003
    Location
    Belgium 3272 Testelt
    Posts
    17,765

    Default

    inventing the wheel again
    select your range to convert and run the code
    Code:
    Option Explicit
    
    Sub swap_names()
    'Erik Van Geit
    '061107
    Dim rng As Range
    Dim arr As Variant
    Dim i As Long
    Dim j As Integer
    Dim ch As Integer
    
    Const sep = ","
    
    If TypeName(Selection) <> "Range" Then Exit Sub
    
    Set rng = Selection
        
        If rng.Count > 1 Then
        arr = rng
        Else
        ReDim arr(1 To 1, 1 To 1)
        arr(1, 1) = rng
        End If
    
        For i = 1 To UBound(arr, 1)
            For j = 1 To UBound(arr, 2)
            ch = 0
            ch = InStr(1, arr(i, j), sep)
            If ch <> 0 Then arr(i, j) = Mid(arr(i, j), ch + 1) & sep & Left(arr(i, j), ch - 1)
            Next j
        Next i
    
    rng = arr
    Erase arr
    
    End Sub

  8. #8
    New Member
    Join Date
    Nov 2006
    Posts
    8

    Default

    Thank you so much! That worked great.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com