Like us on FacebookFollow us on Twitter  Find us on Facebook and Twitter





Welcome to the CDXZipStream August 2015 Newsletter


This month we discuss how to find ZIP Codes within a driving radius, using a combination of straight-line and driving distance calculations.  A custom function is also provided for cleaning up ZIP Code formatting.


Here are the latest blog articles:


In the Spotlight: The CDXZipCode Data Function

A VBA Template for Using CDXZipCode Functions

Finding ZIP Codes in a Driving Radius


A frequent question that comes up from clients is how to identify ZIP Codes within a driving radius of a target ZIP.    CDXZipStream does not have a single function that will do this, but it can be done using both the CDXRadius and CDXRouteMP functions available in the CDXZipStream Basic, Premium, and Premium ACS versions.


As an example, let's find all the ZIP Codes within a 50 mile driving radius of 48911.


First, use the CDXRadius function to find the ZIP Codes within a straight-line radius of 48911.  The choice of distance is somewhat arbitrary so long as it's at least 50 miles:

Radius Dialog 

Since the straight-line radius will always capture more ZIP Codes than the same driving radius, we will be sure to capture all the ZIP Codes we're looking for, and then some.  To narrow down the list we now use CDXRouteMP to calculate driving distance from the target ZIP (48911) to all the ZIP Codes returned from CDXRadius:


CDXRouteMP Spreadsheet


Columns A and B are the ZIP Codes and straight-line distances returned from CDXRadius, and column C contains the driving distances between each ZIP Code in column A and the target ZIP 48911.  (The CDXRouteMP formula in worksheet cell C2 was copied down the entire list.)  


Now just use Excel's sort or filter functions on the Data tab to find only those ZIP's that are within a 50 mile driving distance. When filtering, for example, select a number filter of less than or equal to 50 for column C, and only those rows with distances of 50 miles or less will be visible in the worksheet.


Formatting ZIP Codes Using a Custom Function


ZIP Codes, especially when imported into Microsoft Excel, are often inconsistently formatted.  They can be in either 5 or 9 digit form, contain leading zeros that are often dropped by Excel, and be interpreted as either text or numbers.  These issues can cause mapping-related and other CDXZipStream functions to fail to identify locations. Lack of consistent formatting can also interfere with VLOOKUP formulas that use ZIP codes as lookup values.


You can easily create your own custom worksheet function to clean up ZIP Codes, using a little VBA (Visual Basic for Applications) code.  First open Excel, then use ALT-F11 to open the VBA editor.  From the main toolbar, click on "Insert" to create a new module.  Into this new module copy and paste the following:


Function Zipformat(zipcode)


     Zipformat = Left(Format(Application.WorksheetFunction.Clean(Trim(zipcode)), "00000"), 5)


End Function


This function, which we've called Zipformat, will convert the ZIP code to a 5-digit text format and will remove leading and trailing spaces and non-printing characters.  It will also restore any dropped leading zeroes.  Now you're ready to use Zipformat in worksheet formulas.  


You can apply the formula to a specific ZIP Code like this:




Or use the worksheet cell address of the ZIP Code:



The formula above containing a cell address can then be copied and applied to an entire list of ZIP Codes that needs to be cleaned, as shown here:


Cleaned ZipCodes 


Latest Data Updates


Canadian and U.S. data feeds were updated as of August 26.  There were 134 changes made to the U.S. database this month.  If you would like to update your database to reflect these changes, click on the "License Information and Software Updates" icon on the CDXZipStream toolbar, and select "Data Updates" to login to your account.


We hope you find the information here helpful for you and your organization.  You can contact us with your feedback and suggestions by replying to this email.


The Team at CDX Technologies 

August 2015



Map of the World


In This Issue 


Finding ZIP Codes in a Driving Radius


Formatting ZIP Codes Using a Custom Function


Data Updates









Join Our Mailing List