I see the problem, it is not 3 spaces but it is spaces in the street.

Try this

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
...
Bob the textcolumn scheme doesn't seem to work correctly...here are a few
of
the actual address:
1911 - 290th Street Greenfield
6212 N 131st St Omaha
2773 290th St Orient
2956 North View Road Dorchester
187 Dorchester Drive Dorchester
2770 Schulte Rd Dorchester
1509 Dry Ridge Dr Lansing
2601 LaFayette Ridge Drive Lansing

i've been able to parse the state and zip code out.

i tried the consecutive and did not get the correct results, unless i then
go back and & concatenate the cells to reconstuct the number and street
addr.
i thought the findreplace might do it, no joy so far.
thanks

"Bob Phillips" wrote:

With Text to Columns has a 'Treat consecutive delimiters as one'
checkbox,
so 3 spaces shouldn't matter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

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