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. (Macros must be enabled when using this template.) If desired you can then apply Excel's "Text to Columns" command to parse each zip code into its own cell. Another approach is to use Excel formulas. 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.