Splitting a Column into 3

[Katoomba Blue]

Hi everyone

I'm new to the forum and hope someone would be kind enough to help me please.

I am looking for a formula(s) that would look into a cell and split the data into 3 separate columns.

The original column would be for example 31/01/2007 15000 3600.

The first figure is always a date and the second and third figures are of variable lengths.

Many thanks
Diane

[NBVC]

If the 3 items are separated by a space, then you can use the Text to Columns tool.

Select the column

Go to Data|Text to Columns and select Delimited in the first screen, click Next.

Select Space in the next screen and click Finish.

This should separate your data into the 3 columns.

[Katoomba Blue]

Thanks NBVC for your quick response.

The information in the spreadsheet is imported from an accounts package and is refreshed on a regular basis. In the past this particular column only had 2 entries but now has three. I used the following to split into 2, but now there are 3 entries I am struggling.

Column A = Original data
Column B =+IF(H18="","",LEFT(H18,FIND(" ",H18)))
Column C =+IF(H18="","",MID(H18,FIND(" ",H18)+1,LEN(H18)))

Many thanks

Diane

[NBVC]

Try:

Column B:

Please Login or Register  to view this content.

Column C:

Please Login or Register  to view this content.

Column D:

Please Login or Register  to view this content.

Note. The results are all text values. To convert to actual numbers add a +0 to the end of each formula.

[Katoomba Blue]

Thanks NBVC

Just tried it on my spreadsheet and it works a dream.

Many thank once again for your quick response.

Take care


Diane