globally changing street to st. on records

Donna Marino

I wnat to clean up my database and want to make sure that all of our records show the abbreviated St. Ave. etc. Is there a way to do that?

Joe Moretti

@Donna Marino This would need to be done with an import, where you have a query of all addresses that contain Street, export it to a CVS, then change Street to St. in the file (Excel find and replace) and import that back into the system. BUT be careful with these types of thing since someone could have a last name that contains street. Also when it comes time to do a National Change of Address, the post office has their way of formatting as well. AND another thing you can have a new upper management person who comes aboard and then wants it a different way.

Alex Wong

@Donna Marino
This clean up effort is likely not just for “Street” to “St”, I'm going to assume you want to have Avenue to Ave, Boulevard to Blvd, Parkway to Pkwy, etc.

So one way is like what Joe said, export all addresses into excel, then do a find and replace (taking all abbreviation needed into consideration) and then import back in.

OR

Another option is via a paid service. Blackbaud has AddressFinder sevice that will do this for you at a cost, you will have to ask your account rep for the cost. This service will also take NCOA (National Chnage of Address) info from USPS into account. So it's more than just standardizing address. There are many other service from different vendor our there as well, just less integrated as the Blackbaud service.

Dariel Dixon

@Donna Marino I think it's better to go with a paid service to handle this issue. AddressFinder will do this, as will AddressAccelerator on a smaller scale.

I think if you already have a NCOA service, just have them make the change when importing. The biggest issue is that they may come in as another address as this is a change, but not a material one.

TBH, I am wondering why you are wanting to make this change? Have you had an issue with the mail delivery without the abbreviation? I don't know if this issue is worth the time and effort to fix if it isn't affecting your mail from being delivered properly. Hopefully it is something you can request with your next NCOA lookup.

Faith Murray

@Donna Marino, seconding what @Joe Moretti and @Dariel Dixon both suggested.

If you want to do it cheaply, go with the Import route. I've done this myself cleaning up our database years ago with no complications. The issue of people's names is easily solved by either (1) only importing the street line itself) or (2) highlighting the Address1 column only when you do your Find-Replace. Also make sure to include a space before and after the word to make sure you don't grab compound names: for example, find-replace “ street ” instead of “street”. To be truly compliant with USPS codes, you would need to check all your abbreviations (route, blvd, avenue, suite, road, highway, apartment, etc) as well as your directions (NW, SW, etc), and remove any punctuation in the lines.

If your system is riddled with consistent errors, however, it implies that it has been several years possibly since your last complete Address Update service. This suggests you may have numerous out of date addresses in your file. Rather than a standard NCOA update, you may benefit from an AddressFinder or Enhanced NCOA update at this point, to hunt back 48 months for past moves. A professional data append will not only format your addresses, but ensure that they are current. The one downside, aside from price, is that you may wish to review select records before importing them, or be discriminating in your select query, to avoid inaccurate updates for priests, clerics, college students, and other high-risk move groups.

A middle ground (especially if your mail file is not too large) is to create a free account with the USPS IMSB small business tool. This tool allows you to create your own bulk mail files directly with the post office, and part of the process is that it passes your mail list through the USPS's NCOA service and gives you an output file. As I said, it's free, and you'll get updated addresses back 12 months, already formatted with Blvd, St, Ave, etc. You can process 10K records in one sweep, or split a larger file into multiple sweeps. The one drawback is that it spits it out all in capital letters, so if you don't like capital case, you will need to still convert to proper case, and then Find-Replace certain digits (like Nw to NW, 1St to 1st, etc) before importing into RE.

Donna Marino

@Faith MurrayThank you!

Donna Marino

@Dariel Dixon
Thank you!

Donna Marino

@Alex Wong
Thank you!

JoAnn Strommen

@Donna Marino Just to consider. USPS formatting does not include the punctuation. So we do not include it either. If you use address validation or a similar process, it will remove the punctuation.