Many of our clients use the CDXLocateMP function of CDXZipStream (available in the MapPoint, Basic, and Premium demographic versions) to look up the zip or postal code for an address, or to verify that a given code is correct for an address.   Postal code format varies from country to country, and the amount of postal code information available from CDXZipStream and MapPoint varies as well. 

For a detailed description on how to get missing ZIP or postal codes, see our post Getting Zip Codes for an Address List. 

For a short tutorial on how to use the CDXLocateMP function,  please watch this YouTube video "Address Validation in Excel".  

All industrialized countries use postal codes; in the United States we happen to call them ZIP (Zoning Improvement Plan) codes, or ZIP+4 when an additional 4-digit identifier is used.  However, ZIP codes are unique to the United States, and there is not a universal standard among countries.  For example, both Canada and the United Kingdom use alphanumeric codes, which allow for more variation with fewer characters.   

Since the CDXLocateMP function uses MapPoint as its data source, and MapPoint is a stand-alone desktop software, there are limitations on how much postal code data is available for retrieval.  For instance, there are currently over 800,000 Canadian postal codes alone, and it is just not feasible for MapPoint North America or Europe to include them all.  As a result, requesting a postal code for a given address through CDXLocateMP will in some cases (such as Canada) return only a partial code.   Note that for the United States, with only about 40,000 ZIP codes (not including ZIP+4), all five-character codes are included in the MapPoint database and can be returned through CDXLocateMP.

Here is a table showing examples of the postal code format and the data returned for various countries (where MapPoint uses address find):

There are only two instances, Canada and the United Kingdom (both of which use alphanumeric codes) where the entire postal code is not returned from MapPoint.  For example, for the Canadian address that contains the code “L4M 3A5” only the first three characters are returned.  Note that CDXZipStream does have available for purchase a complete database of Canadian postal codes, although this database is not compatible with the MapPoint find address option.  Please see our blog Canadian Postal Code Database - A New Addition to CDXZipStream for more information on use of the Canadian database option.

Remember that when inputting address data in MapPoint or CDXZipStream, it is important to follow standard address conventions which do vary slightly from country to country, as shown in the table above.  In the U.S., Canada, and the U.K. the postal code is listed just prior to the country, while in most European countries the postal code is listed prior to the city.

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 MapPoint or Basic versions, which work in conjunction with 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.  

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:

= CDXLocate(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:

= CDXLocate(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:

= CDXLocate(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 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 MapPoint is 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 CDXLocateMP 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 MapPoint is also returned.   Here are the possible match types: 

1. Exact - A unique entry was found in MapPoint for this address.

2. Allow Ambiguous-  The first of at least two matching entries was found in MapPoint.

3. Best Match - MapPoint did not find a good match, but this is the best of possible alternatives.

4. Zip Code - MapPoint 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.  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 our video tutorial Address Validation in Excel.

CDXZipStream, our Microsoft Excel add-in that performs zip code, address, and routing analysis, now offers a Canadian postal code database that works with a number of CDXZipStream functions.  Once you’ve purchased the Canadian data, here’s how it can be used:

(Note:  All of the functions here are accessed by right-clicking on any worksheet cell, and selecting “CDXZipStream functions” from the drop-down menu.  Then select the specific function you would like to insert into the worksheet.   You also have the option to manually input these functions directly into the worksheet cells, using the formulas illustrated below.)

CDXDistance finds the straight-line distance between any two zip codes or postal codes.  Just input the zip code or postal codes of interest (alternatively, you can input the worksheet cell addresses of the codes) and click OK. 

The resulting formula will be input to the worksheet:  =CDXDistance (“T0K 0A9”, “L4Y 1Z8”), and display the value 1576.836 miles.   If you use a cell address you can also copy the formula (e.g. =CDXDistance (A1,B1)) to a list of codes, to calculate distances between multiple pairs.

CDXFindZip is a lookup function for zip and postal codes.   Need to find all the zip codes for Vancouver, British Columbia?   If you’ve purchased the Canadian database, you have the option of selecting either a state or province in the first drop-down box:

After selecting the desired city and clicking OK, the resulting formula will be input to the worksheet:  =CDXFindZip("Vancouver","British Columbia"), and all the postal codes will be displayed as a long text string in the format “Code1 | Code2 | Code3 | … “  You can also manually input the formula into a worksheet cell, and if you use cell addresses (e.g. =CDXFindZip (A1, B1)) for the city and province pair, you can copy this formula to apply it to a long list of data.

For a short tutorial on how to use CDXFindZip, please view the video “Zip Code Finder in Excel.”

CDXZipList finds all the zip codes for a U.S. State or Canadian province, U.S. county, or city.  The output can be provided as a single text string (as in CDXFindZip) or an Excel array formula.  The associated city, county, state or province can also be specified as part of the output. 

For a short tutorial on how to use CDXZipList, please view the video “Zip Code Lists in Microsoft Excel.”

If you would like to see how to obtain an array output from CDXZipList for large sets of data, please see our blog “Applying CDXZipStream Arrays to Large Sets of Data.”

CDXRadius finds the list of zip or postal codes within a specified radius area of a target zip or postal code.  Just input the target code, specify the radius area and output options, and whether you want to search either the U.S. or Canadian databases, or both.

If you are working with codes that are close to the U.S./Canadian border, select the “US and Canada” option under the database dropdown list to capture both zip and postal codes within the radius area.  Keep in mind this will slow down the function a bit since it’s searching a much larger set of data;  you also have the option of selecting just “United State” or “Canada” to keep the radius analysis within the country of interest.

If you would like to see how to obtain an array output from CDXRadius for large sets of data, please see our blog “Applying CDXZipStream Arrays to Large Sets of Data.”

CDXClosestZip finds the closest zip or postal code to a target code, especially useful in cases where the closest store location for a customer must be found.  First, specify a customer code and then the Excel range containing the zip or postal codes for every store. 

CDXClosestZip then returns the nearest store code or the distance from the customer. 

You can copy the resulting formula down a long list of customers to determine the closest store for each.  For a short tutorial on how to use CDXClosestZip, please view the video “Zip Code Distance Function in Microsoft Excel.”

Also note, that since the CDXLocateMP and CDXRouteMP functions use the database available through Microsoft MapPoint, these are unaffected by the addition of the Canadian postal code database.  Since the North American version of MapPoint encompasses the U.S., Canada, and Mexico, the geocoding, address verification and routing capabilities of these functions will continue to cover these areas through the data available in MapPoint.

We’ve had a few customer questions lately about how to get zip code radius data for long lists of zip codes.  For example, let’s say you have a list of zip codes and you need the zip codes located within a 10 mile radius of each one.  This can be accomplished quite easily in Microsoft Excel using the CDXZipStream function CDXRadius.

In this example the list of zip codes starts in cell A1 in of a Microsoft  Excel worksheet.   Just  right-click on cell B1, select “CDXZipStream Functions”, then “Insert CDXRadius Function”, and use the following inputs:

Note that for the first zip code the cell location “A1” is used.  The resulting output in cell B1 is a long text string listing all the zip codes, along with their distance from the center, within the 15 mile radius of zip code 10451.  Each zip code and distance pair are separated by a vertical bar “|”:

You can now just copy and paste the formula from cell B1 down column B, all along the list of zip codes listed in column A.

In some cases it is preferable to only include zip codes listed as a string, and exclude the distance data.  We offer a free Excel template that does this automatically, and can be downloaded here.  You must enable macros when using this template.  Another approach is to use Excel formulas to parse the returned string so that the zip codes are shown in individual cells (without the distance data). To do this for the example above, use the following formulas:

In cell C1:  =MID($B1,FIND("|",$B1,1)+1,5)              

- The resulting output will be 10451

In cell D1: =MID($B1,FIND("|",$B1,FIND(C1,$B1))+1,5)                  

 - The resulting output will be 10499

You can then copy the formula from cell D1 along more columns to extract the rest of the zip codes from cell B1.  Repeat for all the rows of zip code data.

Note that the CDXRadius function now works with Canadian postal codes, as well as U.S. zip codes.   If you are working with codes that are close to the U.S./Canadian border, select the “US and Canada” option under the database dropdown list to capture both zip and postal codes within the radius area.  Keep in mind this will slow down the function a bit since it’s searching a much larger set of data;  you also have the option of selecting just “United States” or “Canada” to keep the radius analysis within the country of interest.