Both of our Excel add-ins, CDXZipStream and CDXStreamer, can perform address verification (also sometimes referred to as address correction), which is the process of checking the accuracy of a physical address (i.e. street, city, state and postal code). Address verification is critical in a variety of applications ranging from package delivery to direct marketing, helping to ensure that the delivery of goods and services is timely and cost-effective.
CDXZipStream and CDXStreamer use different approaches and data sources to perform address verification, and we recommend that these be taken into account when choosing which software to use.
CDXZipStream uses the function CDX LocateMP, which accesses the database of addresses available through Microsoft MapPoint. Since MapPoint and the data it contains are updated approximately every 1-2 years and requires separate purchase by the user, we recommend that this approach be used for non-critical functions where a higher rate of unidentified addresses is less important. This is appropriate, for instance, for address verification prior to route optimization using the MapPoint function CDXRouteMP.
To use CDXZipStream for address verification, right-click on any cell in your worksheet and from the dropdown list of CDXZipStream functions, select CDXLocateMP. Provide the input address information as either a single-line or multiple line address. Multiple-line input will look like this:
If parts of the address are unknown, such as state or postal code, they can be omitted from the input data. When specifying what data should be returned, you can request the “Best Match” to return the entire address with all available information. You can also request that just portions of the address be returned, which is useful for identifying a missing ZIP code, for instance, or to accurately parse the address into different worksheet cells. Please see the video below for a short tutorial:
To view the YouTube version: Address Validation in Excel
For long lists of locations, input the worksheet cell address for each portion of the address, and the resulting CDXLocateMP formula can be copied and applied throughout the list. The formula for a multi-line address would look something like this in order to return a Best Match result:
=CDXLocateMP(0,A1,B1,C1,D1)
where the street, city, state and ZIP code data are in cells A1, B1, C1, and D1, respectively. When this formula is copied and applied to other addresses, Microsoft Excel will automatically change the cell references.
CDXStreamer, in contrast to CDXZipStream, uses a USPS database that is updated monthly and is therefore more appropriate for addresses where accuracy is important, such as direct mailing lists. Also, since CDXStreamer is a subscription service where the data resides on CDX Technology servers, the data is always current and no data updates need to be purchased by the user.
To use CDXStreamer for address verification, click on Address Verification on the CDXStreamer toolbar and provide the input data for the first address in your list. As with CDXZipStream, the input can be in the form of a single or multi-line address, and can be actual values or worksheet addresses. Here is how the input looks for a single-line address identified with its cell address:
The resulting formula for the first address entry is:
=CDXAddressVerification("Full_Address_Out", A1)
Note that in the example above, we specify that the full address is returned, which will include the ZIP+4 value if the address is valid. ZIP+4 data are not available through CDXZipStream. Please see our previous post about the value of using ZIP+4 information.
Also, by activating Autocopy in the lower right-hand corner, data will be returned for an entire list so long as it does not contain any empty rows. Use Set Array to Values in order to return values, and not formulas, to the cells; for very large data sets, formulas may slow down Excel unless auto-calculation is disabled.
Please refer to the following tutorial to see CDXStreamer in action:
Here is the YouTube version: Address Correction and ZIP+4 Analysis in Microsoft Excel
CDXStreamer can also be used to provide the input to CDXZipStream functions. For example, as list of addresses can be validated through CDXStreamer, and then optimized using the function CDXRouteMP. This process is described in more detail in the previous post How to Get Better Results with CDXZipStream and MapPoint.
Keep in mind that as with most address verification systems, both CDXZipStream and CDXStreamer verify whether the address is valid, not whether the location or residence physically exists. If a street number for a house, for example, is potentially valid based on the allowed range and numbering system of the specified street, the address is considered valid as a whole.