Results 1 to 8 of 8

Last Name First

This is a discussion on Last Name First within the Excel Questions forums, part of the Question Forums category; I created a list of names in a column: John Smith Jane Doe John Doe Is there a way to ...

  1. #1
    New Member
    Join Date
    Oct 2009
    Posts
    3

    Default Last Name First

    I created a list of names in a column:

    John Smith
    Jane Doe
    John Doe

    Is there a way to flip flop them to change the cell so that the last name is first:

    Smith, John
    Doe, Jane
    Doe, John

  2. #2
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    61,480

    Default Re: Last Name First

    Hello and welcome to MrExcel.

    Try this

    Sheet5

     AB
    1John SmithSmith, John
    2Jane DoeDoe, Jane
    3John DoeDoe, John

    Spreadsheet Formulas
    CellFormula
    B1=RIGHT(A1,LEN(A1)-FIND(" ",A1))&", "&LEFT(A1,FIND(" ",A1)-1)

    Excel tables to the web - Excel Jeanie Html 4
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    New Member
    Join Date
    Oct 2009
    Posts
    3

    Default Re: Last Name First

    You are awesome! It worked. Thank you. That is a long formula. Just so I understand the formula, what does: LEN indicate

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

  4. #4
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    61,480

    Default Re: Last Name First

    LEN() returns the length (number of characters).
    HTH, Peter
    Please test any code on a copy of your workbook.

  5. #5
    New Member
    Join Date
    Oct 2009
    Posts
    3

    Default Re: Last Name First

    You just saved me a lot of time. Thank you.

    Have a good day!

  6. #6
    Board Regular
    Join Date
    Dec 2002
    Posts
    1,179

    Default Re: Last Name First

    A bit shorter formula and not required the LEN function :

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

    Regards
    Bosco

  7. #7
    MrExcel MVP Cindy Ellis's Avatar
    Join Date
    Jun 2006
    Location
    California
    Posts
    1,611

    Default Re: Last Name First

    One complication of name-related formulas is that they don't take into account that some first names have 2 parts and some last names have 2 or more parts, so you may still have to manually adjust some of the results.
    Cindy

    Excel 2007 on XP at work, 2003 on Vista at home.
    If you need to post part of your worksheet, try one of these: Excel jeanie, MrExcel HTML Maker or
    Borders-Copy-Paste

  8. #8
    Board Regular
    Join Date
    Dec 2002
    Posts
    1,179

    Default Re: Last Name First

    But, this formula, I think may be the shortest :

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

    Regards
    Bosco

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