How to Standardize Address Data: Top 6 Methods, Tools, & Tips

Address records are notoriously unreliable, largely because people make a number of spelling and formatting mistakes when they input address data. To fix this, you will need to clean up your lists and standardize the addresses you have on record. Failing to standardize address data can have a significant, negative impact on your data. 

If you haven’t already, go back and learn everything you need to know about address standardization and address matching. Then we’ll dive into how to standardize address data by covering the following:

• Method 1. Using an open-source universal identifier
• Method 2. Standardize addresses using scripts & APIs
• Method 3. Standardize addresses using Excel
• Method 4. Using address standardization software
• Method 5. Standardize addresses with generic business intelligence software
• Method 6. Standardize addresses without a database
  

By the end, you’ll know how to standardize address data, and be able to choose the method that will work best for you. We’ll also explain how Placekey’s solution can eliminate the need for address standardization tools altogether.

6 ways to standardize address data: top methods, tools, & tips

It’s important to note that in most cases, address validation occurs prior to address standardization. Address validation is the process of verifying an email address for authenticity. After this has been done, the address can be standardized according to the format the authoritative database uses. In the United States, addresses are validated against and standardized according to the USPS database.

Any address standardization software must be CASS certified (Coding Accuracy Support System). If purchasing an address standardization software to use, make sure it is CASS certified.

Method 1. Using an open-source universal identifier

Typical address standardization methods rely on closed systems, where you connect to a database via an API or use a software integration to normalize addresses. The problem with this is that these solutions are not open to development from the community, and are limited in their functionality and capabilities.

Alternatively, an open-source solution fosters a rich community of developers and contributors that create better, more intuitive solutions. Rather than focus on standardizing addresses traditionally, consider using an alternative to traditional address matching, verification, and normalization. 

With Placekey, you can gain insights quickly, as all location, geospatial, and address record data is combined using this universal identifier. With this information at your fingertips, you can draw deeper insights and leverage your information for significant impact.

Method 2. Standardize addresses using scripts & APIs

If you can’t use an open-source address standardization tool like Placekey, scripts and APIs are one of the best methods for standardizing address data. Integrate your system with an authoritative database (the USPS in the United States), and write scripts that will find and compare addresses in the records. These scripts will automatically convert incorrectly formatted addresses to the correct format and style.

They do this by first preprocessing (or cleaning) your data for a better comparison. Scripts convert strings to lowercase, remove additional spacing, and even transpose characters to correct common formats such as street labels. 

Once the data has been preprocessed, an API is used to connect to an authoritative address database (the USPS in the US). Addresses are checked against the addresses in the authoritative database, and then corrected to match normalized formats.

Tools to use for scripts & API address standardization

• Python - Python has a library ready to be installed that uses probabilistic address standardization, which allows you to customize your library. Simply use the StandardizeAddresses function. You can also fully code an address standardization solution yourself with Python (if you have the knowledge and experience).
• JavaScript - JavaScript has a library for address validation ready to be installed. It will determine correct spelling and latitude and longitude coordinates. It also returns a list of inexact matches that allow you to catch potentially missed addresses. You can also create your own address standardization solution using JavaScript.
• R - R has a stringr library that can easily be implemented, allowing you to compare strings. Using this, you can standardize address data fairly easily.
• GitHub - This GitHub integration allows you to use R to validate and standardize addresses. It integrates with the USPS API to ensure that your addresses are correct.
• SmartyStreets - SmartyStreets has a variety of easy-to-use APIs that let you validate, standardize, and lookup email addresses.
  

Method 3. Standardize addresses using Excel

Excel is one of the most accessible solutions for business and personal use, as many companies have Microsoft Office at their disposal and most home computers have a version of Microsoft Office. While it is not designed specifically for address standardization, with some tinkering, you can get it to work.

That STANDARDIZE function can be used in Microsoft Excel to get a normalized value from a distribution using mean and standard_dev. To use this, you must use the following syntax:

STANDARDIZE(x, mean, standard_dev)

Where:

• X = The value you want to normalize. (Required).
• Mean = The mean of the distribution. (Required).
• Standard_dev = The standard deviation of the distribution. (Required).
  

As long as standard_dev is equal to or less than 0, STANDARDIZE returns the #NUM! Error value. By entering this into your spreadsheet, you can use an Excel spreadsheet to standardize your address datasets.

Tools to use for Excel address standardization

• YAddress - This Excel add-in can be easily downloaded and used to standardize addresses in your Excel spreadsheets.
• geocodio - Input Excel spreadsheets with address data, and have geocodio parse and standardize your address data for you.
• excel VBA Master - This Excel-based solution allows you to copy and paste address data directly into this fully formatted and already set-up spreadsheet. It will automatically standardize addresses according to USPS formats.
• CDX Technologies - This free Excel template lets verifies, parses, and corrects address lists for you, making the process easy and accessible.
• AddressDoctor Excel Plugin - AddressDoctor parses your address lists using algorithms, correcting data to standardized formats to ensure accuracy in your datasets.
  

Method 4. Using address standardization software

Address standardization software refers to solutions that standardize address data, most commonly in accordance with an authoritative database (such as the USPS). In most cases, these solutions also validate addresses for authenticity as well. 

These solutions come essentially ready-to-use, designed specifically for address standardization purposes. In theory, these will take very little work to configure for address matching, as that is their purpose.

To do this, you will need to choose an address standardization software that has the compatibility to integrate it with your solutions and the functionality to perform the tasks you care about most. 

Download and install the tool you pick. You can then input CSV files or directly link the tool to your own records using an API or SDK, allowing it to standardize your address lists.

Address standardization software to use:

• SmartyStreets - SmaryStreets is CASS certified, and it standardizes addresses according to the USPS formatting. SmartyStreets has SDKs readily available for Ruby, Python, Java, and more so you can set up and go!
• ArcGIS Pro - Input raw or tokenized address lists, and let ArcGIS standardize addresses for you, combing your data for flaws and correcting them automatically.
• MapInfo Pro - This GIS system is designed for geocoding and address analysis. Provided by Precisely, a business intelligence solution with a range of functions and potential use cases.
• Data Ladder - Data Ladder understands the costs of bad address data, and offer services that verify and standardize addresses so you can save money on errors caused by poor address data.
• Datavolve - Datavolve is BCC Software’s address validation and standardization solution, helping you save time and money, retain customers, and grow your business.
• Melissa - A leading in data quality for 2021 according to G2, Melissa’s address standardization services are simple to use and reliable, making them accessible and effective.
  

Method 5. Standardize addresses with generic business intelligence software

Business intelligence solutions include a range of software and technologies designed for data analysis. These solutions are built to be powerful, effective for business-level problems, and often offer a range of features and functionality. In most cases, these solutions not only track and report on data, but predict future trends and help guide your decision-making.

While these solutions are not exclusively designed for address standardization, the capabilities they offer typically make it possible for you to perform address standardization. Some may come with solutions pre-built for these purposes and others will not. In that case, your team will need to build their own address standardization solution using the core business intelligence software.

Business intelligence tools to use for address standardization

• Tableau - This business intelligence and data management tool allows you to compare records, which can be used to standardize addresses. 
• Power BI - This business intelligence solution is designed specifically for Microsoft, integrating seamlessly with Microsoft Office and Azure.
• PRAGMATIC WORKS - Standardize, verify, and deduplicate address data using PRAGMATIC WORKS pre-built USPS Address Verification tool.
• KAVIGLOBAL - KAVIGLOBAL is a business intelligence solution that has address standardization capabilities, which will standardize and parse address data for you.
• RunnerDQ - RunnerDQ’s CLEAN_Customer solution allows you to maintain clean customer data so you can make clear insights throughout their customer lifecycle.
  

Method 6: Standardize addresses without a database

You can standardize addresses without using a database or any specially designed software. Doing this manually takes a lot of time and effort though, even if you’re using spreadsheets and other technology to aid you.

To standardize without a database:

1. Get the address that you wish to standardize.
2. Identify any spelling and formatting errors.
3. Correct address spelling and formats according to the USPS standard.
4. Correct capitalization and letter casing (USPS will accept Proper Case and UPPERCASE formats).
5. Add the ZIP+4 wherever possible.
6. You’re done!
   

To do this manually, you will physically need to review a table of suffixes (or suffix abbreviations) used by USPS. Determine which is the correct suffix to use according to the USPS standards, and then correct the address. 

Similarly, you will need to review the formatting of the entire address, and ensure that it follows the correct formatting for USPS. You will also need to make sure the address is either in Proper Case or UPPERCASE, also checking spelling while doing this.

Essentially, you will need to manually reference your address against the authoritative database. While this isn’t extremely complicated, it is tedious and can be very time-consuming.

Choosing the best solution will depend on the software and tools you currently use, and the future direction of your business. If you want to get away from having to standardize addresses, check out Placekey.

Placekey uses a universal identifier for location coordinates that encodes location, address, and point of interest data into a single Plackey. These identifiers are not susceptible to common address input issues like misspellings, street abbreviation formatting, and much more, as they use a standardized, coded system. 

They also allow you to encode multiple points of interest at a specific address, which can be used to identify a Starbucks within a Target or a specific business office within a skyscraper.

All address data is standardized prior to being encoded into a Placekey, and Placekeys are regularly checked for duplicates. This entirely eliminates the need for you to standardize address data, so you can focus on what you do best!