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
    63,541

    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
    63,541

    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,714

    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 2010 on Windows 7 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