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.

The U.S. Census Bureau has recently released the Census 2010 Summary File (SF-1) that includes a compilation of the data by Zip Code Tabulation Areas (ZCTAs).  ZCTAs are an approximation of the U.S. Postal Service zip codes and are actually aggregates of small census-defined areas called blocks.  Since ZCTAs generally match zip code areas very closely, for the purposes of this article we will use the terms ZCTA and zip code interchangeably.  For more information on ZCTAs, please see our blog posting “What’s a ZCTA?”  

Zip code data is especially valuable for market research, target marketing, and sales planning, since zip codes represent a fairly small (on average zip codes cover about 9,400 people) homogenous area that can be matched to readily available mailing address lists.  And since the decennial census is a 100% count of the population, census data provides a truly complete snapshot of the American population as a whole. 

To see the data from the new Summary File, go to the on-line American Factfinder tool and select the 2010 SF1 100% Data Set under Topics.  You can then select ZIP code/ZCTA as a geographic filter and find the specific geography and data table of interest.  For a list of zip codes more typical of a customer mailing list, however, it is easier to use our software CDXZipStream which has a selection of the more important demographic variables from the 2010 Census.  These variables, covering age, population, households, and race data, are: 

1. Population

2. MalePopulation

3. FemalePopulation

4. MedianAge

5. MedianAgeMale

6. MediaAgeFemale

7. HouseholdsPerZipcode

8. PersonsPerHousehold

9. AsianPopulation

10. BlackPopulation

11. HawaiianPopulation

12. HispanicPopulation

13. OtherPopulation

14. IndianPopulation

15. WhitePopulation

These are part of the Premium Zip Code data feed, available with the Demographic, Premium, and Premium ACS versions of our Excel add-in software CDXZipStream.  Please see the version comparison webpage for more information on other features of these versions.

To obtain data for these fields for a list of zip codes in Microsoft Excel, place your cursor on the first zip code and click on the CDXZipStream logo on the main toolbar.  Just select the fields of interest, and click “Get Data”.  The data is returned (with optional field headings) like this:

You can also view the YouTube Video “Demographic Data in Excel” for a quick demonstration of how to get demographics for long zip code lists using this method.

Microsoft Excel has a variety of methods for formatting zip codes, enough to be a bit confusing to the non-expert user (which covers just about most of us).  Fortunately,  CDXZipStream, our Excel add-in for analyzing address and zip code information, can handle both five-digit and nine-digit (+4) formats and can also automatically assume that a leading zero exists even when Excel drops it from a zip code (e.g. 08030 turns into 8030).  Nevertheless, for viewing and printing purposes, you will want to be able to control the appearance of zip code data using one of the following techniques:

1. Force the cell contents to be viewed as text using an apostrophe

In this case just simply enter an apostrophe before the cell contents, and this will force Excel to display the entry as text “as is” regardless of the cell formatting.  For instance, a cell with general or number formatting will display the entry 08030 as 8030, but by using the apostrophe like this (‘08030), the leading zero will be retained.   This is a good approach if you have a fairly short zip code list.

2. Use the zip code formats provided with Excel

Excel has built-in five and nine-digit formats that you can access from the Format Cells dialog box, under the Number tab.  Under the Special category, select the Zip Code or Zip Code + 4 format that works best for your data:

3. Make your own custom format

From the Format Cells dialog box shown above, under the Number tab, select the Custom category and under Type, input 00000 or 00000-0000 for a five or nine-digit code, respectively.  Leading zeroes will be retained.

4. Use the TEXT cell function

This requires the use of a formula in a nearby cell.  For example, if the first zip code in your list is in cell A1, type the following in cell B1:

= TEXT (A1, “00000”) 

This automatically converts the value to text, regardless of how the cell is formatted, and retains leading zeros for the first five digits of the code.  You can then paste the formula down the rest of your list to convert all the zip codes to this format; use autofill for very long lists.

Keep in mind that all the data returned from CDXZipStream are returned as text.   If you are using the LOOKUP cell function with this returned data, then the lookup values, such as zip codes, must also be in text format.  (This applies to finding zips in a radius area, as illustrated in our YouTube video “Find Zip Codes in a Radius Using Excel”).   In this case use options 1 and 4 above, which forces the zip codes to text independent of cell formatting; and for very long lists, use option 4.