SUBSTITUTE using arrays?
 

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 useles
and repetative words like RD, LANE, PKWY, NORTH, SOUTH, NW, AVE.
etc... and basically be left with "123 Maple 405" instead of "123 S
Maple APT # 405".

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

I have used the SUBSTITUTE command, but it seems to work on only on
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 i
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 singl
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 th
source text is.

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

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

SUBSTITUTE using arrays?
 

You should use a macro for this. Record the action while you do it.

--
Don Guillett
SalesAid Software

"alpo " wrote in 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/

SUBSTITUTE using arrays?
 

I meant to use edit REPLACE and if you want to do it again, record a macro.

--
Don Guillett
SalesAid Software

"alpo " wrote in 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/

SUBSTITUTE using arrays?
 

Hi
SUBSTITUTE does not accept an array as second parameter. You have to
nest multiple SUBSTITUTES like
=SUBSTITUTE(SUBSTITUTE(B1,C1,""),C2,"")

Note: there is limit to the maximum level of nested functions. in your
case you may consider creating a user defined function which does this
kind of cleaning


--
Regards
Frank Kabel
Frankfurt, Germany


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/

SUBSTITUTE using arrays?
 

Hi

This seems like a "once and for all" operation, so you don't have to usea
formula.

In menu Edit Replace, is there a "Replace All" buton ? That should do the
trick faster than setting up and securing a formula solution.

HTH. Best wishes Harald

"alpo " skrev i melding
...
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/

SUBSTITUTE using arrays?
 

Unfortunately, this is not a one time function. I will be dealing wit
at least 10000 customer addresses per day. Yes, I work for a ver
large company. I will try the macro route for now

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

SUBSTITUTE using arrays?
 

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/

.

SUBSTITUTE using arrays?
 

"alpo <" wrote...
...
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".

Unless your addresses are all from locations in very small towns (unlikely if
you have appartments with units numbered 405 and/or streets qualified with SW),
stripping off 'useless' words is extraordinarily dangerous unless you really do
want to chance a pretty thorough data screw up. Where I live there are different
streets names Oak Road, Oak Street, Oak Lane, Oak Court and Oak Circle. There
are also numbered streets running from 2nd Street to 63rd Street from the center
of the city going north, East 8th Street through East 38th Street from south of
the center of the city going east and 1st Avenue through 107th Avenue heading
south. These are all separate streets, and between them 14th Street, East 14th
Street and 14th Avenue run through most of the city's zip codes. Are these
qualifiers 'useless'?

What do you believe you're going to accomplish by deleting these qualifiers?

It's nice to know, though, that there are so many helpful souls ready to show
you how to destroy your data integrity.

--
To top-post is human, to bottom-post and snip is sublime.

SUBSTITUTE using arrays?
 

"Harlan Grove" skrev i melding
...
It's nice to know, though, that there are so many helpful souls ready to
show
you how to destroy your data integrity.

I usually get smacked because I won't give the customer what she asks
for -"it's not good for you". Now this <vbg

Best wishes Harald