A common spreadsheet problem is separating address information into multiple columns.  For instance, if an address is entered as a text string "street, city, state zip code" in a single cell, it is often necessary - and can make address analysis so much easier - to split the street, city, state and zip code into separate cells. A common technique to accomplish this involves parsing on the basis of delimiters, such as commas, in the text.  This involves entering formulas, in some cases quite complex, to separate out the data based on the position of the delimiters.  This will work as long as the data is consistently formatted, but that often isn’t the case.

Our Microsoft Excel add-in, CDXZipStream, provides a much easier, more reliable method for parsing addresses.  The CDXZipStream function CDXLocateMP, working in conjunction with Microsoft MapPoint, can take an address text string and return a variety of information about it to the spreadsheet. You may be most familiar with this function for geocoding an address to find its latitude and longitude.  But CDXLocateMP also provides street, city, state and zip code as distinct outputs which can be placed in individual cells.  This doesn’t require you to understand delimiters and can handle entries with inconsistent formatting.

As a bonus, this function only returns data for valid address entries in MapPoint. If an address is found to be invalid, a message to that effect is returned to the spreadsheet.  Since address verification occurs simultaneously with parsing, using CDXZipStream can be a great first step for tackling large address lists in Excel.

Note:  For a quick tutorial on address verification using CDXZipStream, please watch the YouTube video Address Validation in Excel.  In this case latitude and longitude data are returned to the worksheet, but you could just as easily request street, city, state, or zip code to both parse and verify the data at the same time.

Distance calculations are put in use when estimating freight costs, identifying closest customers and for many other business purposes. One popular application is a radius analysis, using distance to determine the nearest customers in a list to a central location. For convenience, most calculations use distance between zip codes.

Calculation of the distance between zip codes is based upon latitude and longitude information for each zip code. This geocoding data is determined by calculating the centroid of boundaries of each zip code. You can think of a centroid as a balance point, a location near the center of the areas of a polygon that make up the zip code.

CDXZipStream and other software used for distance and radius analysis use this geocoding information to calculate a straight line distance to a chosen zip code. All possible combinations of zip codes are reviewed and then sorted in ascending distance to show the closest ones. This type of analysis is fine when trying to point a customer to your nearest location, and every location is in a separate zip code.

But you should be aware of the possible inaccuracies. The land area in many zip codes can be hundreds of square miles. In fact the largest one in Tonopah, Nevada is 5,496 square miles. So if a site is on the periphery of a zip code distance calculations can be many miles in error. If you would like to know the land area associated with your zip code you can find this in our demographic database, along with water area and hundred of other items.

So what do you do if you need a more precise analysis? The solution is to determine exact locations by address, city and state for all locations. With this latitude and longitude information you can then determine the true, not approximated distance. CDXZipStream has a straight line calculation, CDXDistance2WP, to calculation distance between locations. CDXRouteMP can be used to calculate driving distance between addresses. So even evaluating multiple sites in the same zip code is no longer a problem. We’ll go into more detail on doing this in a future article.

We'd like to take the opportunity to talk about the CDX Technologies free on-line tool for getting demographic data by zip code.  Just input a zip code and specify the report you need – general data about the location, demographics, population gender, or race – and a clear and concise report is returned to the web page.  You can also export the data to a Microsoft Excel spreadsheet or presentation-quality PDF file, which is easy to send as an email attachment or include as part of a larger report.

This tool is currently a work-in-progress; we'll soon be updating it with the latest census information as it becomes available, and would also like to solicit opinions from our clients and readers here for suggestions on how we can make it even better.  Drop us a line at support@CDXTech.com and let us know what you think!

We also enthusiastically recommend a web tool called American FactFinder, provided courtesy of the U.S. Census Bureau.  Not only does it draw on a huge stockpile of demographic data (such as the ten-year census, American Community Survey, and latest Economic Census), it's a pretty quick and reliable source of information when you need data for a small number of geographies.  It's especially well-suited for getting very detailed, esoteric data which are not generally available from other free online tools.  We don't recommend using it when you need data for many locations (in that case try our Excel add-in CDXZipStream), but for limited data it's a good place to start looking.

Note:  Coming in January of 2011, American FactFinder will get a new look and some new functionality, including new table and mapping features.  Hopefully the tables will be easier on the eyes and little less crowded with information.

So, with a nod to an old joke – "In God we trust, all others bring data" – consider getting your demographic data (free!) from the great online tools mentioned here.

In a past post we briefly discussed (in an admittedly negative way) the ribbon GUI that first appeared in Excel 2007 and is now entrenched in all Office 2010 applications.  Well, around here we started to talk a little more about it and it's pretty clear that the ribbon is one of those things (like anchovies on pizza or the New York Yankees) that you either love or hate – there's not a lot of middle ground. 

The real issue is that the ribbon is a major hurdle to many Excel users who would like to upgrade to a later version of Excel to take advantage of new features.  So let's talk about the ribbon in a little more detail and see if we can help with the decision-making process in upgrading to either Excel 2007 or Excel 2010.

So, what about that gosh-darned ribbon?  First of all, if you really don't want to use the ribbon, you don't have to.  Third-party vendors have stepped in with relatively inexpensive (about $15 to $30) add-ins that can recreate the classic toolbar in either Excel 2007 or 2010.  Some versions can be used in all Office 2010 applications and can even be deployed among multiple computers; in at least one case (ToolbarToggle) the toolbar is customizable just like in older versions of Excel.     

Keep in mind, however, that many people do like the ribbon and you should probably consider trying it before buying a toolbar add-in.  If you decide to use the ribbon, be prepared to spend some time learning it.  We don't recommend doing this by the seat-of-your pants – sit down at your computer with your favorite caffeinated drink (you'll need it) and go through some good training materials.  Some possibilities are the Chandoo.org Master the Excel 2007 Ribbon free learning guide, and Microsoft's Up to Speed with Excel 2007 learning tool.

But beware:  Microsoft decided (what were they thinking?) that the ribbon in Excel 2007 could only be customized using xml (Extensible Markup Language) and programming code.  Many Excel users save precious time and significantly improve their productivity by customizing that classic toolbar to fit their particular needs; did Microsoft expect the typical user would want to learn xml too after going through the ribbon learning curve?  You can also purchase add-ins that do this programming for you, but fortunately this is not an issue with Office 2010 and the ribbon can now be customized as easily as the classic toolbar.

There are lots of other things to consider if and when you decide to upgrade. You can find more than a few discussions on the web weighing the pros and cons of the various versions of Excel, and that is beyond our scope here.  Ultimately, many users who are perfectly happy with their trusty old software (remember Windows 98?) will upgrade simply because of the inconvenience of using an outmoded version, especially with the loss of technical support and compatibility. At the end of the day, there are thankfully a few inexpensive workarounds to address most concerns about the ribbon – it's just too bad that something as basic as the user interface had to be an issue at all.