Colum B:
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
C:
=FIND("^",SUBSTITUTE(A1," ","^",2))
D:
=FIND("^",SUBSTITUTE(A1," ","^",B1-2))
E:
=FIND("^",SUBSTITUTE(A1," ","^",B1))
F:
=LEFT(A1,C1)
G:
=MID(A1,C1+1,D1-C1)
H:
=MID(A1,D1+1,E1-D1)
I:
=RIGHT(A1,LEN(A1)-E1)

If you need a numeric value in I, use the VALUE() function.

Of course(!) you can leave out all of the intermediate columns and construct
huge formulas to obtain the 4 answers straightaway. Ask others for help!

--
Kind regards,

Niek Otten

"nospaminlich" wrote in message
...
QUOTE "I have a series of entries in Column A like this:

28 December WH SMITH GB LICHFIELD 28 December 4.98
30 December TESCO STORE 2842 GB CARDIFF 30 December 33.36
31 December ASDA PETROL- UPT 4170 GB PEMBROKE 31 December 32.50
02 January ICELAND GB NEWPORT 02 January 6.00

.... and I want to split the data across 4 columns e.g. B:E which would
be
Date, Name, Date2, Amount." QUOTE


Thanks a lot.

I've cracked the easy ones - First Date and Amount with the following
formulas in B2 and E2 respectively:

=LEFT($A3,FIND("^",SUBSTITUTE($A3," ","^",2)))

=RIGHT($A3,LEN($A3)-(FIND("^",SUBSTITUTE($A3,"
","^",LEN($A3)-LEN(SUBSTITUTE($A3," ",""))-1))))

However, I'm now really struggling with the formulas to get the Name and
2nd
Date from the middle of the string.

Any ideas, please?