Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Aug 2002
    Location
    Hillsboro, Oregon, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Moving last names to another column (Excel 2000 SR1)

    In an older message, someone posted an easy way to separate first and last names when they had been entered in one column, separated by a comma. I want to do that now, <img src=/S/compute.gif border=0 alt=compute width=40 height=20> but after scanning all 16 pages of Excel posts, have determined that this was one of the posts that was eliminated. <img src=/S/bwaaah.gif border=0 alt=bwaaah width=123 height=15> Does anyone have this information? <img src=/S/please.gif border=0 alt=please width=31 height=23>

    Thanks,
    Tia

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

    Re: Moving last names to another column (Excel 2000 SR1)

    A simple way is
    1. Make sure that there is an empty column to the right of the column with names.
    2. Select the column with names.
    3. Select Data | Text to Columns...
    4. In the first step of the wizard, select Delimited.
    5. In the second step, select the Comma as delimiter.
    6. Click Finish.

    If you have to do this often, you can create a macro to do it for you. Post back if you need this.

  3. #3
    Star Lounger
    Join Date
    Aug 2002
    Location
    Hillsboro, Oregon, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving last names to another column (Excel 2000 SR1)

    Thanks. That's easier than what I figured out, which was to copy the column to Word, convert to text, then convert back to columns with the comma as the separator. Then copy and paste the columns into 2 empty columns in Excel. I didn't think I could do it from within Excel. I like your solution better.

    Tia

  4. #4
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving last names to another column (Excel 2000 SR1)

    I often run into this problem so here are three notes I made to which I often refer. You may need to adjust them to meet your needs but there are written "conceptually":
    ==================================================
    Excel - last name sort

    Put the names in a spreadsheet starting in A1. Then, place the formula below in B1 and replicate it all the way down until the last name in the list is covered.

    =RIGHT(A1, LEN(A1)-FIND(" ", A1))

    When the all the names have been done, select all the full names and all the last names. Then, choose (from the menu) Data, Sort. Sort on column B.
    ================================================
    Excel - lastname, firstname -> firstname lastname

    Put the names in a spreadsheet starting in A1. Then, place the formula below in B1 and replicate it all the way down until the last name in the list is covered.

    =RIGHT(A1, LEN(A1)-FIND(" ", A1))&" "&LEFT(A1, FIND(",", A1)-1)
    ================================================== ===========
    Excel - lastname, firstname -> lastname firstname (two columns)

    Put the names in a spreadsheet starting in A1. Then, place the formulas below in B1 and B2 then replicate them all the way down until the last name in the list is covered.

    B1 =LEFT(A1, FIND(",", A1)-1)

    B2 =RIGHT(A1, LEN(A1)-FIND(" ", A1))

Posting Permissions

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