Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Montreal, Quebec, Canada
    Posts
    261
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automatically change 'Smith, Bob' to 'Bob','Smith' (2000)

    Every month they give us a new phone list of all the people on my project. The first column is Lastname, Firstname. I find this annoying as I know very few last names since we're all on a first-name basis. I know how to manually separate the two names into two columns to resort them then concatenate them back together, but I'm wondering if anyone has a formula to do this automatically.

    Ideally, I'd like to let folks keep using the sheet as it is, but put in a second tab that reformats the phone list, pulling the data from sheet 1.

    What do you think?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automatically change 'Smith, Bob' to 'Bob','Smith' (2000)

    If you have a name in the format "Lastname, Firstname" in A1, the formula
    <code>
    =TRIM(MID(A1,FIND(",",A1)+1,100))&" "&TRIM(LEFT(A1,FIND(",",A1)-1))
    </code>
    will return the name in the format "Firstname LastName. This formula can be filled down.

    If you want to place the formula on a different sheet, use
    <code>
    =TRIM(MID('Name Sheet'!A1,FIND(",",'Name Sheet'!A1)+1,100))&" "&TRIM(LEFT('Name Sheet'!A1,FIND(",",'Name Sheet'!A1)-1))
    </code>
    where "Name Sheet" is the sheet containing the phone list.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Montreal, Quebec, Canada
    Posts
    261
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Wow

    Hans, you are a frickin' <big>genius</big>!
    Thanks!

Posting Permissions

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