[SOLVED] Find/Replace or VBA

[reno]

I have downloaded 600 name address list from web. i'm tryin to parse the
address. an address example is show here:
2449 63rd St Vinton
503 W 6th St Vinton
5378 - 29th Avenue Vinton
525 C Ave. W Norway
2414 76 St Watkins
There seem to be 5 blank spaces between the last of the physical addr and
the start of the city. Find/Replace does not seem to work, the text/col
doesn't recognize the 5 spaces.

Any ideas?
Thx

[Dave Peterson]

Since you got them from the Web, maybe they're not spaces--they could be those
troublesome HTML non-breaking spaces (x160).

Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.htm

If those 5 "spaces" are the only char(160)'s in the cell, you may be able to
just:

Select the range
data|text to columns
delimited by other
hit and hold the alt key while typing 0160 using the numeric keypad
and check that box to "treat consecutive delimiters as one"

And finish up that dialog.



reno wrote:
>
> I have downloaded 600 name address list from web. i'm tryin to parse the
> address. an address example is show here:
> 2449 63rd St Vinton
> 503 W 6th St Vinton
> 5378 - 29th Avenue Vinton
> 525 C Ave. W Norway
> 2414 76 St Watkins
> There seem to be 5 blank spaces between the last of the physical addr and
> the start of the city. Find/Replace does not seem to work, the text/col
> doesn't recognize the 5 spaces.
>
> Any ideas?
> Thx

--

Dave Peterson

[reno]

you're right on the chr.
it shows
Dec 160 160 032 160
between the street addr and start of city.
suggestions?
thx


"reno" wrote:

> I have downloaded 600 name address list from web. i'm tryin to parse the
> address. an address example is show here:
> 2449 63rd St Vinton
> 503 W 6th St Vinton
> 5378 - 29th Avenue Vinton
> 525 C Ave. W Norway
> 2414 76 St Watkins
> There seem to be 5 blank spaces between the last of the physical addr and
> the start of the city. Find/Replace does not seem to work, the text/col
> doesn't recognize the 5 spaces.
>
> Any ideas?
> Thx

[Dave Peterson]

Select the range
Edit|replace
what: spacebar, then alt-0160
with: (leave blank)
replace all

Then use data|text to columns using alt-0160 as the delimiter.

reno wrote:
>
> you're right on the chr.
> it shows
> Dec 160 160 032 160
> between the street addr and start of city.
> suggestions?
> thx
>
> "reno" wrote:
>
> > I have downloaded 600 name address list from web. i'm tryin to parse the
> > address. an address example is show here:
> > 2449 63rd St Vinton
> > 503 W 6th St Vinton
> > 5378 - 29th Avenue Vinton
> > 525 C Ave. W Norway
> > 2414 76 St Watkins
> > There seem to be 5 blank spaces between the last of the physical addr and
> > the start of the city. Find/Replace does not seem to work, the text/col
> > doesn't recognize the 5 spaces.
> >
> > Any ideas?
> > Thx

--

Dave Peterson

[reno]

Dave you're the Man, that worked!

Thanks!!
"reno" wrote:

> you're right on the chr.
> it shows
> Dec 160 160 032 160
> between the street addr and start of city.
> suggestions?
> thx
>
>
> "reno" wrote:
>
> > I have downloaded 600 name address list from web. i'm tryin to parse the
> > address. an address example is show here:
> > 2449 63rd St Vinton
> > 503 W 6th St Vinton
> > 5378 - 29th Avenue Vinton
> > 525 C Ave. W Norway
> > 2414 76 St Watkins
> > There seem to be 5 blank spaces between the last of the physical addr and
> > the start of the city. Find/Replace does not seem to work, the text/col
> > doesn't recognize the 5 spaces.
> >
> > Any ideas?
> > Thx