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

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

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

  1. #1
    New Member
    Join Date
    Nov 2006
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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,828
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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 (2010-2016)



  3. #3
    Board Regular ChrisUK's Avatar
    Join Date
    Sep 2002
    Location
    England, The Lake District
    Posts
    675
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Redmond, WA
    Posts
    29,498
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    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

  5. #5
    MrExcel MVP
    Moderator
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    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,832
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    I love Jesus

    email Erik

    founder of DRAFT

    my free Addins
    Table-It download & info
    Formula Translator 04

  7. #7
    MrExcel MVP erik.van.geit's Avatar
    Join Date
    Feb 2003
    Location
    Belgium 3272 Testelt
    Posts
    17,832
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    I love Jesus

    email Erik

    founder of DRAFT

    my free Addins
    Table-It download & info
    Formula Translator 04

  8. #8
    New Member
    Join Date
    Nov 2006
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank you so much! That worked great.

  9. #9
    New Member
    Join Date
    May 2010
    Location
    Hertfordshire U.K.
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Hope there is a way.....
    I have entered my contacts into Outlook 2010 and exported them as a Windows CSV file to my desktop, I entered the contacts in all relevant fields as First Name and Last Name; John Smith but the exported CSV file has these as Last Name-Comma-First Name. The only way I can do this at present is to insert two additional columns B & C along side the A column showing the name as Smith, John.
    Using the formula I found on this thread (Thanks Guy's!) of =TRIM(MID(A2&" "&A2,FIND(",",A2)+1,LEN(A2))) in column B I can convert back to John Smith, I then have to Copy Column B and Paste the results as Value into Column C.
    I can then delete columns A & B and save. Then I can import into Google Contacts and sync with my Mobile (Cell) phone.

    This seems a very long winded way to transferring data. I do not know if the problem lies with the way Outlook exports the data format or the way the CSV file imports the data format. It leaves Outlook as John Smith but gets swopped around on the way over!

    Any idea where to start looking for a way to get John Smith into the CSV file correctly?

    I would attach a couple of screenshots but it seems the forum does not like me
    so I am not allowed this function:

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


    Look guys, I am a nice pensioner with a dodgy back, polite, well spoken and a carer as well, what's not to like?
    Last edited by AJW1100; Dec 16th, 2017 at 07:12 AM. Reason: Typo with fat fingers

  10. #10
    New Member
    Join Date
    May 2010
    Location
    Hertfordshire U.K.
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

      
    Hi. Anybody have any idea how to sort this problem, I would really love to get it sorted and any help would really be appreciated. One picture is worth a 1000 words but I cannot upload any.

    Happy Christmas to all.

User Tag List

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