# Thread: separating text from one colum two columns (2003)

1. ## 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. ## 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. ## 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
•