Find/replace
 

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

Find/replace
 

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

Find/replace
 

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

Find/replace
 

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

Find/replace
 

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

Find/replace
 

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