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.

There is nothing in the text string I can use as a delimiter so I need to
build a formula. I've tried using Left and Right to at least split out the
first date and amount but I'm struggling as even these strings are of
different lengths.

Please could someone help me with formulas which would work for each of the
4 sections in the string.

Many thanks in anticipation

You could insert your own delimiters using Find & Replace several times
on the same column. Highlight column A then CTRL_H then:

Find: "ember "
Replace with: "ember_"
click Replace All

will pick up quite a lot of dates, but you could repeat with

CTRL-H
Find: "uary "
Replace with: "uary_"

and similarly for March, April, May, June, July, August, October, each
time replacing the space at the end with the underscore (depending on
how many months you have in your data). You seem to have GB in each
description, so you could replace " GB " with "_", then finally " 28 "
with "_28 ", " 29 " with "_29 " etc for the second dates.

Obviously, you do not type the quotes in the "Find" or "Replace with"
boxes.

When you have replaced everything you can then use Text to Columns with
underscore as delimiter.

Hope this helps (though a little tedious).

Pete

Hopefully you can rely on only one space, never two next to eachother.
In column B, enter the total number of spaces in the string:
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
In column C, find the position of the third space from the right:
=FIND("^",SUBSTITUTE(A1," ","^",B1-2))
C was just an example. But this way you can find the first date, the price,
the second date and then the rest, no matter how many spaces there are in
it, is the name.
Takes some time, but try. If you can't get it, post again.

--
Kind regards,

Niek Otten

"nospaminlich" wrote in message
...
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.

There is nothing in the text string I can use as a delimiter so I need to
build a formula. I've tried using Left and Right to at least split out
the
first date and amount but I'm struggling as even these strings are of
different lengths.

Please could someone help me with formulas which would work for each of
the
4 sections in the string.

Many thanks in anticipation

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?

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?

That's brilliant. Thanks a lot Niek.