If all substrings separated by space(s) go to separate columns then use
DataText to columnsseparated by spaces!

Regards,
Stefi

€žMcP€� ezt Ã*rta:

I have a list of UK addresses in the format below, spaces separating, no
commas or other chars.

a1 = "1 high street leeds yorkshire"

I would like to be able to split the individual words/no.s out into cells of
their own, so that....

b1 = 1
b2 = High
b3 = street
b4 = leeds
b5 = yourshire

I can actually do some of this, eg =find("space char",a1) that will give me
pos 2
then I can do z1= LEFT(a1,2 -1) so pick the left number one char.

My problem comes in working out how long the string is (yes I know LEN, I
mean the substring from one space to the next space )

I want to be able to say, ok pos 2 is a space, where is the next one?
I then need to say the next part of the string is from pos 2 +1 to get the
character(if it is one), not the space, but I then need the length of b2 in
this instance, and so on until the end of the string.

This would possibly not be (too) fiddly if all the addresses were the same
length, and the addresses perhaps did not have multiple spaces at the end ( a
possibility, though I could concatenate an XX char so I know where it ends.

Can anyone suggest any subroutine code I could use in excel to do this simply?
Thanks in advance.
MP