Space delimited text to multiple columns

**mogdog1981** · 06-09-2010, 08:36 AM

Hi,

I know how to split text from one column into multiple columns.

However, the record in row 4 (See attached) creates three columns (David, J and Osborn), which overwrites the data in column E.

Is there a way that I can stop this happening? i.e. tell excel to have David J in one column and Osborn in the other whilst still splitting all of the other records without an initial?

Thanks

**NBVC** · 06-09-2010, 08:45 AM

Use formulas...

In C2: =LEFT(B2,IF(ISNUMBER(SEARCH(".",B2)),FIND(".",B2),FIND(" ",B2)-1))

copied down and in D2: =TRIM(SUBSTITUTE(B2,C2,""))

You can then copy and Paste Special|Values over the formulas if you want to get rid of them.