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





Welcome to the CDXZipStream December 2013 Newsletter


We have a new template that automatically geocodes (gets latitude and longitude) for a list of addresses, and sorts these locations based on their distance from a target location.   Using geocoded data is more accurate than ZIP code-based analysis.  We also show you a very useful Excel command called "Text to Columns" that can quickly parse ZIP code strings from CDXZipStream into separate worksheet cells.


 Here's our latest blog articles:


- Using Flash Fill in Excel 2013 to Parse and Concatenate Addresses

- Setting the MapPoint Version by Region or Year


Geocode and Sort by Distance in a New Free Template 


We now offer a free template that will both geocode (determine the latitude and longitude) of a list of addresses, and then sort those addresses based on their distance from a target location.  All CDXZipStream free templates can be downloaded from our links page.

Geocode Map 

This new template can be used for a wide variety of applications, such as identifying the closest customers to a store location, the closest patients to a hospital, or the closest delivery points to a warehouse.  


The template uses latitude and longitude to calculate distances, and results are very accurate.  In cases where accuracy is less important, you can also use our free Radius Analysis template which calculates distances based on the ZIP code centroid location.  Since ZIP codes in some parts of the United States can span very large areas, we recommend using the geocoded results from this newest template to reduce error in critical analyses.


The template requires use of the CDXZipStream MapPoint version or higher, as well as separate purchase and installation of Microsoft MapPoint.  You can evaluate the template using both free trial versions of CDXZipStream and MapPoint.  Please see this offer from our recommended reseller if your need a purchasing source for MapPoint.   


The Geocode and Sort by Distance template is an abbreviated version of our Bulk Radius Analysis template.  The Bulk Radius Analysis template will also create customized reports showing the closest locations for multiple targets.  For more information, please refer to the article Store Locator and Bulk Radius Analysis in Microsoft Excel.


Using the "Text to Columns" Command to Parse CDXZipStream Data


There are a couple of functions in CDXZipStream, CDXFindZip and CDXZipList,  that return a list of ZIP codes as a text string to a single cell in Excel.  The ZIP codes are all separated by a vertical bar called a pipe delimiter that looks like this: "|" .  For cases where you need to parse these ZIP codes into their own individual cells, you can use the Excel command "Text to Columns'.  Here's how it works:


First, select the list of cells you need to parse.  Copy and paste these cells as values, to remove the underlying CDXZipStream formulas.  If you haven't done this before, please refer to the more detailed directions here


In Excel 2007 and higher, from the Data tab click on the Text to Columns icon in the Data Tools group. (In Excel 2003, select Data from the control bar, then Text to Columns.)  In the first dialog box choose Delimited, and press Next.  In the next dialog box, select Other under Delimiters and in the adjoining text box fill in the pipe delimiter character "|".  Press Finish, and the result should look like this:

Text to Column OutputYou may need to format the destination cells as ZIP codes to see leading zeros; go to the Format Cells Number tab and under the Special category select the ZIP code type.  If parsed data exceeds the number of columns available in the worksheet, you will receive a warning message before parsing proceeds.  For more information about the Text to Columns command, please see this Microsoft article.  

Latest Data Updates


Canadian and U.S. data feeds were updated as of December 12.  There were 32,468 changes made to the U.S. database this month.  Most of these changes were a result of a technical change in processing the data that caused some changes to the order of the Area Code data. For example, if a record had Area Code 845/914, it might now be 914/845. The data is still the same, but the order has changed.  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.  Please contact us with your feedback and suggestions by replying to this email.


The Team at CDX Technologies 

December 2013



Map of the World


In This Issue

Geocode and Sort by Distance in a New Free Template


Using the "Text to Columns" Command to Parse CDXZipStream Data


Data Updates