Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Location
    Atlanta
    Posts
    10

    Unanswered: separate names and last name in column

    Hey guys,
    I know this is probably a very easy one but I can't figure out how to do this.

    I just need to break up data in a column that contains last_name, first_name. I just want to create 2 columns, one with the last name and the other with the first name.

    I appreciate any help!
    Breban
    Last edited by breban; 04-15-04 at 10:52.

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    For first name use this in b1

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

    For last name use this in C1

    =RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-
    LEN(SUBSTITUTE(A1," ","")))))
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Mar 2004
    Location
    Atlanta
    Posts
    10
    I figured out how to do it with 'text in columns'. I knew I'd kick myself once I figured it out!

    Thanks anyway!

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091

    Wink

    Been there - done that!

    Never hurts to have another tool in the arsenal, though.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Oct 2003
    Location
    Missouri, USA
    Posts
    18

    Question

    Shades ... where would I put this code for the first name and last name if I want it to apply to all rows in column B and all rows for column C?

    Also, is it possible to still have titles on the columns if you want all the rest of the rows for that column to use the same code to come up with first and last names?

    Thanks in advance,
    Maggie

  6. #6
    Join Date
    Oct 2003
    Posts
    1,091
    If you want to have column headings (in row 1), then your data begins in A2,

    For first name use this in b2

    =LEFT(A2,FIND(" ",A2)-1)

    For last name use this in C2

    =RIGHT(A2,LEN(A1)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-
    LEN(SUBSTITUTE(A2," ","")))))

    Then copy B2 and C2 down as far as the data. Easy way is when they are selected is to move the cursor to lower right corner and when it turns to black lines, double-click. It will automatically fill to however many cells are filled in Column A.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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