Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    separating text from one colum two columns (2003)

    Hi,

    I have a spreadsheet with column B that has the first and last name listed in it. There are no comma's separating the last name from the first name, only a space. For example:
    SMITH JOHN & JANE
    DOE JOE

    The user needs to have the last name in one column and the remainder of the name(s) in another column. What would I use for a formula to do this? It's been ages since I did it last and it was based on a comma after the first name.

    Thanks,
    Leesha

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

    Re: separating text from one colum two columns (2003)

    Let's say that the data begin in cell B2.
    In cell C2 (or any other cell in row 2) enter the formula
    <code>
    =LEFT(B2,FIND(" ",B2)-1)
    </code>
    And in the cell to the right of it:
    <code>
    =MID(B2,FIND(" ",B2)+1,100)
    </code>
    Select these two cells and fill down as far as needed.

    Explanation: FIND(" ";B2) returns the position of the first space in cell B2, so if B2 contains SMITH JOHN & JANE, it returns 6. This is used in combination with the LEFT and MID functions to return the part before and after the space. The argument 100 in the MID formula is an arbitrary number larger than the length of the longest name that you expect.

    Note: the formulas won't take double last names into account.

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: separating text from one colum two columns (2003)

    Hi Hans!

    That worked beautifully. These poor people were hand typing 8000+ rows and our IT Engineer said "I'll be Leesha can do it". HA!! Ya right! I couldn't bear to think of this poor person doing these by hand. They will be so thrilled! I REALLY appreciate the explanation. This one I'm going to have to digest!

    Thanks again,
    Leesha

Posting Permissions

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