A common spreadsheet problem
is separating or "parsing" 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 Excel, 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.
Fortunately,
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.
