Just use Text to columns with the space as the separator. You example seems
to imply that you will always have at least 10 fields separated by a space.
Always keep the last nine items separate.

If text to columns yields 10 items keep the ten items
if text to columns yields 11 items concatenate the first two
if text to columns yields 12 items concatenate the first three

Your example yields 15 items:
1 C
2 W
3 RUSSELL
4 HAULAGE
5 &
6 PLA
7 5023
8 1
9 1
10 JCB
11 31-May-05
12 51
13 Hours
14 18
15 918

so concatenate the first six.

--
Gary''s Student


"SL" wrote:

I hope someone can help as this is driving me up the wall.

I have a text file that imports into Excel with data for each row in the
same cell. I need to split this data out into separate columns. The string is
a random length depending on the data in it. The example below shows the
string as it is, the following one shows how it should be split up into
separate columns.

"C W RUSSELL HAULAGE & PLA 5023 1 1 JCB 31-MAY-05 51 Hours 18.00 918.00"

C W RUSSELL HAULAGE & PLA
5023
1
1
JCB
31-MAY-05
51
Hours
18.00
918.00

The string will always be in the order above but will be different lengths
depending on the data within that row.

I have been trying to use the occurence of the first number to indicate the
end of the 1st part and go from there but have not had much success. There
will be a random number of spaces in the first part of the string depending
on the name of the supplier.

Any pointers would be greatly received.

Thank You

Regards

Sonya