Splitting first and last names

Here's a fun one:

I've been handed a big excel file that has a lot of names, addresses, city, state, etc. Unfortunately the person who entered all the data neglected to break first and last names into separate columns. Is there some way to do a search and cut, say, looking for the space between the names, to move

COLUMN 1
David Thomas

into

COLUMN 1 COLUMN 2
David Thomas

Any ideas?

Thanks again folks,

Darrell Leland

Hi,

You can use a FIND() function to detect the position of the space between words, and then slice the string accordingly. Alternatively you could use the Text to Columns functionality to do it for you.

It also depends on how many parts to a name you have. Are they all just two words names as your example, or could there be three, four or more?

Rgds

Are they all just two words names as your example, or could there be three, four or more?

Richard's question is a very good one given your scenario, but assuming that your example is true for all instances these formulas should work:

Column 1:

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

Column 2:

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

Hope that helps.

I had completely forgoten about text to Columns. Thanks guys.

Darrell Leland