In cases where address data is missing zip codes, you can use our Microsoft Excel add-in, CDXZipStream, to quickly and easily get them for you, even for long lists of addresses. Here’s how:
You will need to use either the CDXZipstream Bing or Basic versions, which work in conjunction with Bing or Microsoft MapPoint to verify the address location and determine the correct the zip code. Just right-click on any worksheet cell in Microsoft Excel, and select the CDXZipStream function called CDXLocateMP or CDXLocateBing.
Address information can be input as a single string (single-line address), or separately as street, city, and state (multi-line address). In the example, above, we’ve input a single text string as the address. You can also input the worksheet cell location of the address (e.g. A1), since CDXZipStream automatically recognizes that the value of the worksheet cell is the address string. Similarly, for a multi-line address, you can input cell locations for each street, city, and state component.
After selecting zip (Postal code) as the returned data, and clicking OK, a formula is placed in the worksheet:
= CDXLocateBing(7, “501 Cooper Landing Rd, Cherry Hill, NJ”)
Where the “7” parameter indicates that zip code data is being requested. This cell displays a value of 08002 as the correct zip code.
If you have a long list of addresses that require matching zip codes, make sure you input address data using the worksheet cell locations, so that copying the formula to the rest of the list will result in zip codes being returned for all addresses. In this case the first formula would be something like this:
= CDXLocateBing(7, “A1”)
Where A1 is the cell location of the first address. Similarly, if street, city, and state information are located in cells A1, B1, and C1, then the formula would be:
= CDXLocateBing(7, “A1”,”B1”,”C1”)
When copying and pasting the first formula (or using Excel’s autofill feature), Excel will automatically adjust the cell references so that the formulas are correct for all rows of the list.
Note that Bing and MapPoint does not use additional address data such as suite or apartment numbers in its analysis. If suite or apartment numbers are part of the address, the address should be formatted so this information is provided after the street number. For example, "501 Cooper Landing Rd, Apartment 2A, Cherry Hill, NJ" is acceptable.
Since Bing and MapPoint are programmatically looking for the best match for each listed address to one in its own database, it’s important to know the quality of the match that was found. You can do this by using CDXLocate to return the best match address found. Select “best match” as the output, and then use Excel worksheet formulas to compare the result to the original.
The best match for this address was returned to column C. (The actual CDXLocateMP formula is shown in the second row). In column D, a formula was used to check the match with the original address in column A; if a mismatch was found, a value of 1 would be returned; an exact match would return a 0, as shown. Note that since the zip code was missing in the original address, the found zip code in column B needed to be appended to the original address to perform the check.
You can also use our free geocoding template to quickly determine the type of match found by MapPoint. (All our templates can be downloaded from our links page.) Just copy and paste your address list into the template (zip codes are optional), and click on the “Get Coordinates” control button. The template is designed to return latitude and longitude data for each address, and the type of address match found by Bing MapPoint is also returned. Here are the possible match types:
1. Exact - A unique entry was found for this address.
2. Allow Ambiguous- The first of at least two matching entries was found.
3. Best Match - MapPoint or Bing did not find a good match, but this is the best of possible alternatives.
4. Zip Code - MapPoint or Bing could not find any matches, so the centroid of the zip code is returned from CDXZipStream
Any address that has a Best Match or Zip Code match type should be double-checked for accuracy.
For addresses in Canada, MapPoint cannot return an entire postal. Bing can return the full Canada postal code. There are over 850,000 Canadian postal codes as compared to about 43,000 U.S. zip codes, and only the first three digits of the Canadian postal code (called the FSA, or Forward Sortation Area) are available using the CDXRouteMP function working with MapPoint. For example, the CDXLocateMP formula (which includes country data):
=CDXLocateMP(7, “1385 Bank Street, Suite 203, Ottawa, Canada”)
Will return “K1H” as the FSA, although the full six digit code is “K1H 8N4”. Note that the first letter of an FSA code corresponds to a particular "postal district", which, outside of Quebec and Ontario, covers an entire province or territory.
For more information about the CDXLocateMP function, please also refer to the video below:
or our YouTube tutorial Address Validation in Excel.