Hi alpo ,

It would be a good idea to rethink the necessity for the
string purging you describe. That is unless something
like "Northplane Lane" resolving to a value of "p " is OK.

In working on what is probably a similar project I found
that removing (Only for comparison purposes) any spaces
and other special characters made working with addresses
more practical - in my case I'm matching 20,000+ name
listings from 2 different legal entities (Different people
keying the data with different perspectives and ideas of
spelling and abbreviations) and consolidating into a
single 20 field listing for use by all involved. It's
unlikely you can succeed with a full 100% match. In the
case of the source data I have, I'm finding a 95.5% match
only because there are 6 data fields commonly used by both
sources.

Best Regards,
Walt

-----Original Message-----
What I need to do is a massive search-and-replace-type
thing.

Specifically, I have a ton of addresses. I want to edit
out the useless
and repetative words like RD, LANE, PKWY, NORTH, SOUTH,
NW, AVE.,
etc... and basically be left with "123 Maple 405" instead
of "123 SW
Maple APT # 405".

I have a nice column of items to be deleted, but I can't
figure out how
to apply that field to my other field of addresses.

I have used the SUBSTITUTE command, but it seems to work
on only one
cell at a time. I need to specify ranges of cells.

Let's assume my addresses are in a1:a1000 and my "bad"
phrases are in
c1:c25. My output column will be B1:b1000.

In B1, I can put:

=SUBSTITUTE($A1,$C$1,"")

I can copy it down the column and it works fine for a
single
substitution (C1). But, what I need to do is something
like this:

{=SUBSTITUTE($A1,$C$1:$C$25,"")}

Except that the above line doesn't work. It outputs
exactly what the
source text is.

Am I going about this the completely wrong way or do I
just need to
tweak it a little?


---
Message posted from http://www.ExcelForum.com/

.