maybe word-wrap, try

in one column, get the street with

=LEFT(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-
LEN(SUBSTITUTE(A1," ",""))))-1)

in another, get the city with

=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-
LEN(SUBSTITUTE(A1," ",""))))+1,255)


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"reno" wrote in message
...
i inserted the formulas and got a #Value error, now i'm really confused.
not
sure why the error message. i've 3x-ck the formula and it seems to be
correct.
i appreciate your help on this!

"reno" wrote:

have a listing of 500 mailing type labels downloaded from web. i i want
to
parse into columns... problem with the address portion e.g.,
123 W Main St Columbus, Oh 46555
18759 W 1700th Rd New Providence, OH 46555

There seem to be 3 spaces between the ending of the street addr and the
start of the city as the only consistency in the scheme.
i want to try to use find/replace and put delimiter in, then use the
text to
column. F/replace doesn't seem to recognize more than one space??
Any ideas?
Thanks