Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Substitute Respective Entries In Arrays | Excel Discussion (Misc queries) | |||
Substitute | Excel Worksheet Functions | |||
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE.... max. limit 8 :( | Excel Discussion (Misc queries) | |||
SUBSTITUTE | Excel Worksheet Functions | |||
substitute for = | Excel Worksheet Functions |