|
  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:
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:
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)
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:
|
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
|
|
|
|
|
In
This Issue
Finding ZIP Codes in a Driving Radius
Formatting ZIP Codes Using a Custom Function
Data Updates
|
|
