Converting to Standardized USPS Abbreviations

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to match up location records received from separate sources using
address information, and want to eliminate the duplicate records, but I would
have many more matches if I could convert all the addresses to some standard
format first, (so different spelling and/or abbreviations are not
overlooked).

I know I can do this one at a time on the USPS website, but I was hoping to
find a way to do this in a batch process. Unfortunately, I have no budget for
fee based services, and that seems to be the only route indicated on the USPS
website.

I have a table of the USPS standard address abbreviations and can probably
accomplish what I need by building a series of Replace() functions, but
before trying to reinvent the wheel I thought I would check if anyone has
already done this.

Does anyone know of an existing utility, or any suggestions for how to
convert mailing address data to standardized address format including the
USPS preferred abbreviations?

Thanks in advance for any suggestions.

TK
 
Hi TK,

I'm not familiar with the USPS's ideas of standardisation, but I guess
that you're looking at stuff like replacing "Avenue" and "Ave" and
"Ave." and "Av." with a single form - but without touching the "Avenue"
in
North Avenue Drive
and changing
1234 North Avenue Drive Apt. 345
to
1234 N Avenue Drive, #345
without changing the "North" in
1234 North Road

This can get tricky! If you search the newsgroups (e.g. at Google
Groups) for
nurick horse hockey
you'll find a VBA function that uses the VBScript regular expression
engine to parse most US "street" address lines into their component
parts. (IIRC it doesn't yet handle addresses with a postfix quadrant,
e.g. "613 K Street, N.W., Ste 230").

It should be possible to adapt this to make replacements only in the
parts of the address that need it.
 
Thanks John,

I'm starting to appreciate how this could indeed get tricky, but I'll give
it a go with your code to control what I try to 'standardize'.

TK
 
Back
Top