Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Nov 2001
    Location
    Exeter, New Hampshire, USA
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cell formatting (Excel 2000\ SR1)

    Hi there,

    I've got a range of cells that list peoples names, first name then last name. Does anyone know if its possible to have Excel change them so they are listed as last name first name?

    Thanks

    Troy

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Cell formatting (Excel 2000\ SR1)

    If you have a given delimiter between first and last name, the following formula should work if the name is in A1.

    <big>=RIGHT(A1,LEN(A1)-FIND("<font color=red>*</font color=red>",A1)) &"<font color=red>*</font color=red>" & LEFT(A1,FIND("*<font color=red>*</font color=red>",A1)-1)</big>

    Replace the red aserisk (*) with the character that seperates the first and last name, eg a comma or a space.

    This may only be suitable for straight forward firstname-lastname situations, without any initilas etc.

    Andrew C

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Cell formatting (Excel 2000\ SR1)

    How are they separated? If by a comma or some consistent punctuation, you could use something like the Template I'm attaching. This was for some salespeople who were recording contact info in Excel and then needed to take it into Contact Management software.

    Cheers
    Attached Files Attached Files
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  4. #4
    Star Lounger
    Join Date
    Nov 2001
    Location
    Exeter, New Hampshire, USA
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell formatting (Excel 2000\ SR1)

    Thanks very much Andrew. It worked but I have one more minor question. How would I tweak the formula so that it left the names as last name, first name (note the comma)? Currently they are listed as first name last name (no comma).

    Thanks

  5. #5
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell formatting (Excel 2000\ SR1)

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

    Note the change between the two ampersands. &"*"& becomes &","&

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Cell formatting (Excel 2000\ SR1)

    Troy,

    try this, which assumes a space between firstname and lastname.

    <big>=RIGHT(A1,LEN(A1)-FIND(" ",A1)) &"," & LEFT(A1,FIND(" ",A1)-1)</big>

    Andrew

  7. #7
    Star Lounger
    Join Date
    Nov 2001
    Location
    Exeter, New Hampshire, USA
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell formatting (Excel 2000\ SR1)

    Worked great. Thanks to all for your quick help. This board is the best!!

Posting Permissions

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