CDXStreamer is our Microsoft Excel add-in that performs address correction and verification as well as data retrieval for ZIP+4, ZIP+4/TIGER and Canadian postal codes. Here we will walk through the simple process of correcting postal address lists, which can virtually eliminate undeliverable addresses and significantly reduce the costs associated with incorrect address data. A free demo (no credit card required) is available at https://www.cdxtech.com/cdxstreamer/free-trial/.
Let’s say we have a long address list in Microsoft Excel, which could be addresses of customers, service call locations, delivery points, or other locations. Here’s a portion of the list (in Column A):
Select an empty cell next to your address list, and then use the cursor to click on the "Verify Address" icon on the CDXStreamer toolbar, as seen here in Excel 2010:
A dialog box will pop up:
Input the cell location of the first address in your list, in this case cell A2. In this example the addresses are each listed within a single cell, but CDXStreamer can also handle addresses where the components are in separate cells. In this situation, just select the “Multi-Line Address” option, and input the separate cell locations for the street, city, state and ZIP code.
Now we choose the type of data to return to the worksheet. Here we request the ZIP+4 code, also referred to as the 9digit_zip. We also select the AutoCopy option so data will be returned for all the addresses in our list (so long as it doesn’t contain any empty rows). Click OK, and the returned data in our worksheet looks like this:
In cases where an address match could not be found in the database, an error is returned. All addresses that return N/A (an example in row 11 is highlighted in red) should be manually checked.
CDXZipStream will also correct addresses that contain limited errors, such as misspellings, incorrect or missing zip codes, cities, or states, and incorrect street suffixes and directionals. Standard US Postal Service formatting, such as abbreviations and capitalization, will be also included. To perform address correction, repeat the process described above, but specify that the entire address (full_address_out) be returned. This will also include the full ZIP+4. For the list shown above, the revised addresses are:
Besides formatting and abbreviation issues, here are some of the corrections made using CDXStreamer:
91 Gurnet Road, Brunswick, Maine (Missing ZIP code)
Correction - 91 GURNET RD, BRUNSWICK, ME 04011-9328
41 Sherman Road, St. Johnsbury, VT 05819 (Incorrect street suffix)
Correction - 41 SHERMAN DR, SAINT JOHNSBURY, VT 05819-9280
46 Cheltingham Ave., Schenectady, New York 12308 (Incorrect ZIP code)
Correction - 46 CHELTINGHAM AVE, SCHENECTADY, NY 12306-5106
200 MacIntyre Road, Pittsburgh, NJ 15237 (Street name misspelled)
Correction - 200 MCINTYRE RD, PITTSBURGH, PA 15237-4035
202 Kings Highway West, Haddonfield, NJ 08033 (Incorrect directional)
Correction - 202 KINGS HWY E, HADDONFIELD, NJ 08033-1905
320 W Ottawa St., Lansing, MN 48922 (Incorrect state abbreviation)
Correction - 320 W OTTAWA ST, LANSING, MI 48933-1590
3810 Forbes Ave., Philadelphia, Pennsylvania 15260 (Incorrect city)
Correction - 3810 FORBES AVE, PITTSBURGH, PA 15260-6995
Street address formatting in CDXStreamer follows the US Postal Service "one component failure rule" to find a correct address match. If not more than one address component mismatch exists, where a "component" is defined as a predirectional, street name, street suffix, or a postdirectional, then a correct address can be returned. For instance, the address “500 North Main Street West” has four components. If one of the components has to be added, changed, or deleted to achieve a unique match, CDXStreamer will correct the address and return the ZIP+4 code. If more than one component is incorrect, no match is allowed. Also, no match can be returned if adding, changing or deleting a component results in multiple matches. The priority is to add a suffix first before adding a directional. If no match exists, the suffix is changed or deleted, and then the directional.
If there is not an exact match within the city, the "one component failure rule" is applied within the input ZIP code. If there is still no match, the rule is applied to all addresses within the postal finance number, and is only considered invalid if there is no match within that.
For a short tutorial covering address verification and address correction using CDXStreamer, please refer to the video below:
As an alternative the same tutorial is available on YouTube: Address Correction and ZIP+4 Analysis in Microsoft Excel