Find/replace

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

Bob the text>column 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 find>replace might do it, no joy so far.
thanks

 

[Bob Phillips]

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)

 

[Guest]

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!

 

[Bob Phillips]

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)