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





Welcome to the CDXZipStream November 2016 Newsletter


We've just introduced a free CDXZipStream template that automates the CDXClosestZip function.  The template can identify the first, second, or third-closest ZIP Code to a target ZIP, and also provides the distance between ZIP code and target.  An added feature of the template is that any data associated with the identified ZIP Code (such as store number, city, sales region, etc.) is returned. It works with both U.S. and Canadian ZIP and postal codes.


We also review a few strategies to use when ZIP Code formatting, specifically when Excel drops leading zeroes, causes problems when using Bing Maps or MapPoint-related functions.

The New Closest ZIP Code Template


We now offer a new free template that automates the CDXZipStream function CDXClosestZIP.  From a list of ZIP Codes, this function identifies the ZIP Code that is closest to a target ZIP, based on straight-line "as-the-crow-flies" distance.  The template works with long lists of target ZIPs, and is ideal for finding the closest stores to customers, the closest distribution centers to delivery points, the closest health care facilities to patients, etc.  It also works with Canadian postal codes.  Here is a short tutorial:


Find the Closest ZIP Code to a Target ZIP

Find the Closest ZIP Code to a Target ZIP



The template can identify the first, second, or third-closest ZIP Code, and provides the distance between ZIP code and target.  An added feature of the template is that any data associated with the identified ZIP Code (such as store number, city, sales region, etc.) is returned.  It can also run a validity check to identify any input ZIP Codes that cannot be found in the application database.


The template works with U.S. ZIP or Canadian postal code data within CDXZipStream, and requires CDXZipStream Lite or higher.  The Canadian data option is an additional purchase.  You can download the free template from the CDXZipStream template area;  it is compatible with both purchased and trial versions of CDXZipStream.


How to Fix ZIP Code Formatting Issues


When entering ZIP Code data into an Excel spreadsheet, you may have noticed that Excel will drop leading zeroes, such that the ZIP Code "07869" will appear as "7869".  This often occurs in a new spreadsheet where the default cell formatting is "General" number formatting, so Excel treats the ZIP Code as a number instead of a text entry.


In most CDXZipStream functions, such as CDXDistance and CDXRadius, the leading zero is automatically assumed during calculations involving any four-digit ZIP Code entries.  This is not the case for functions using Bing Maps or MapPoint, since postal code formatting in various countries do include legitimate four-digit codes.   For example, when using CDXRouteBing to calculate the driving distance between two ZIP Codes with dropped leading zeroes, the following error will occur:


Format Zip Codes



Reformatting the data using Excel's ZIP Code format (under the "Special" category) will show the leading zeroes again in the cells, but will not change the CDXRouteBing error.


Here are some ways to correct this problem:


1.  Use the TEXT function.  If the ZIP Codes are already in your spreadsheet, use the text function in another column to convert the data to a 5-digit text entry, using the following formula:  = TEXT(A1, "00000") for a ZIP Code in cell A1.  Copy this formula down the column to convert all the data.  Then copy the results as values into the original column of data.  


2.  Format the destinaton as text.  Before entering ZIP Codes into the spreadsheet, format the destination column as text.  Then copy and paste the data as values (so as not to change the formatting of the destination).  If the original set of data were text entries showing the leading zeroes, they should be retained after the paste operation.


3.  Use a custom function.  You can easily create your own custom function to restore ZIP Code leading zeroes. 

In Excel, use ALT-F11 to open the Visual Basic for Applications (VBA) editor.  From the Insert menu of the editor, select "Module".  In the new module window that opens, copy and paste the following:


Function ZIPFORMAT (zipcode)

ZIPFORMAT = Left(Format((zipcode), "00000"), 5)

End Function


From within your worksheet, use the function like this:  = ZIPFORMAT(A1) to reformat the ZIP Code in cell A1.


If your ZIP Codes originate from a database, they may contain non-printing characters that can also cause problems.  To remove non-printing characters, use the CLEAN function.  In this case the VBA formula is as follows:


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


Latest Data Updates


Canadian and U.S. data feeds of CDXZipStream were updated as of November 28.  There were 678 changes made to the U.S. database this month.  If you would like to update your database to reflect these changes, select the "Account" option on the CDXZipStream commandbar and click on "Check for Available Database Updates".  You can then update each database as required. 


CDXZipStream updates are a part of the one-year software maintenance subscription included with your purchase.  If you would like to extend your subscription, please sign in to the CDX Technologies website and from the account area for CDXZipStream, select "Buy Data Update".


As always 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 

November 2016



Map of the World


In This Issue 


The New Closest ZIP Code Template


How to Fix ZIP Code Formatting Issues


Data Updates