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 see the video below:
or watch the YouTube version: 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.