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
oups.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