I would definitely go with Ron's suggestion, Excel is not very good at
parsing regardless if you parsing names or addresses given all the options


--
Regards,

Peo Sjoblom



wrote in message
oups.com...
indeed, would also like use ln, dr, wy, etc
any suggestions?
mordy


On Aug 2, 4:05 pm, "Peo Sjoblom" wrote:
Oops, didn't see that you wanted to split it in 3 places

to get the last part use

MID(A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,1024)

Put that in D1 now for the second string in C1 with the first in B1 and
the
last in D1 use

=TRIM(SUBSTITUTE(SUBSTITUTE(A1,B1,""),D1,""))

I haven't really tested it a lot but should work for most cases given
only 3
options (what about blvd, Ln, Dr etc?),

--

Regards,

Peo Sjoblom

"Peo Sjoblom" wrote in message

...



If there are only these three options and if they have a space before
and
after

=LEFT(A1,FIND("^^^",SUBSTITUTE(SUBSTITUTE(SUBSTITU TE(LOWER(A1)," rd
","^^^")," st ","^^^")," ave ","^^^"))+3)

will return the first string, then assuming that you put this formula
in
let's say B1

=TRIM(SUBSTITUTE(A1,B1,""))

--

Regards,

Peo Sjoblom

wrote in message
roups.com...
hi,

i have a string containing an address/town/zip code and want to split
them in seperate columns.
example:
123 ocean rd smalltown 2134
into:
123 ocean rd | smalltown | 2134

i don't know to do the first split as its position varies but the
street always ends with rd, st, ave. can that be of any help? can
someone help me?

regards
mordy- Hide quoted text -

- Show quoted text -