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 the Canadian database with these functions, please view the video below:
For the YouTube version, please see: Canadian Postal Code Analysis in 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.