Globally Change Address i.e St to Street

Koly Namdarian 2

Hi


I am trying to do a query on globally changing address names

• St to Street
• Dr to Drive
• Gr to Grove
• Crt to Court and so forth

It seems to be including other address names 

In the query cant do 'equals' or cannot do 'contains'


**Help**


Cheers


Koly

JoAnn Strommen

If I were doing this type of address change I would Export the address data to excel. Be sure to include the constituent ID(s) and Address Import ID. In Excel use search and replace which is much more user friendly. Searching for "St" you can make case sensitive if you know they are all capitalized. Just review each find and click 'change' or 'next'.Then import the changes back in as updates.

Jen Claudy

I suggest you include spaces in [Find & Replace]...find " St" and replace with "Street"...otherwise, you'll end up changing "Rainstorm St" to "Rainstreetorm Street"...of course, you could still get " Sterling St" to " Streeterling Street".


I think there is an option in AddressAccelerator (if you have that module) where you can set it to the spelled out versions...probably worded something like "non-USPS abbreviations" because USPS prefers that you use St instead of Street (in addition to no punctuation, single address line, and all caps rather than proper casing...most places I know who follow USPS guidelines follow everything except the all caps).

Paula Petrowski

You may want to keep the "St" rather than Street etc as those are preferred by USPS for mailings.

Catherine Burns

Koly Namdarian:

Hi


I am trying to do a query on globally changing address names

• St to Street
• Dr to Drive
• Gr to Grove
• Crt to Court and so forth

It seems to be including other address names 

In the query cant do 'equals' or cannot do 'contains'


**Help**


Cheers


Koly

 

Just a further warning - does St always mean Street in your data or might it sometimes mean Saint? Could Dr ever mean Doctor?


I have inherited several databases where this has been done and done badly so I have Street in the middle of words like the "Streeterling Street" example below. If you decide to do it then be very careful. Excel will allow more checks before re-importing than a Global Change.


I prefer to make such changes using proper address cleaning software (or a service) that fixes typos and other errors at the same time.

Koly Namdarian 2

Hi 


Thank you for your responses, it is greatly appreciated

Yes they all mean Street

I had a feeling it wasn't possible

Now I can let the team know

)


Koly

Koly Namdarian 2

oh i meantn the global change wasnt possible hehe

i can try the other ways )


Koly Namdarian:

Hi 


Thank you for your responses, it is greatly appreciated

Yes they all mean Street

I had a feeling it wasn't possible

Now I can let the team know

)


Koly

 

Koly Namdarian 2

Hi 

Thank you for your responses, it is greatly appreciated
I had a feeling the global change wasnt an option
Now I can let the team know
)

Koly

Cathleen Mai

You would need to choose "whole words only" in the Edit Change Parameters. See here:

http://www.blackbaudknowhow.com/the-raisers-edge/the-raisers-edge-cleanup-series-address-standardization.htm


I do the Street changes with Excel (because we do have Saint as St...) and everything else with Global change.

I change smaller batches and doublecheck after. It is a little scary, because the query includes all the things you do not want to change but the "whole words only options" really works ok. Try it out with a small group.

Andrew Ludwell

JoAnn Strommen:

If I were doing this type of address change I would Export the address data to excel. Be sure to include the constituent ID(s) and Address Import ID. In Excel use search and replace which is much more user friendly. Searching for "St" you can make case sensitive if you know they are all capitalized. Just review each find and click 'change' or 'next'.Then import the changes back in as updates.

At the risk of making this more complicated than it should be, you can do robust find and replace using IF functions in Excel.


As long as the St or Gr is at the end of the address line string, something along these lines would work:


=IF(RIGHT(<address_1>,3)=" St",CONCAT(LEFT(<address_1>,LEN(<address_1>)-3)," Street"),"")


Please note the spaces in front of " St" and " Street", and <address_1> refers to the cell in the same row where the address information is stored.

The final double "" is the output for addresses that don't end in " St", and you can then nest into there (replacing the "") further IF statements for the rest of the addresses which end in " Gr" etc.


Good luck!