CDXZipStream, our Microsoft Excel add-in for ZIP code and location-based analysis, includes a variety of functions that are accessible by right-clicking on any cell in your worksheet. In this article we’d like to focus on the CDXZipStream function called CDXLocateMP, which is available in the CDXZipStream MapPoint, Basic, and Premium Demographic versions. CDXLocateMP can be used for:
ZIP Code look-up
County, census tract, and MSA (Metropolitan Statistical Area) look-up
CDXLocateMP works with Microsoft MapPoint running in the background to access MapPoint functionality directly from Microsoft Excel. To use CDXLocateMP, just right-click on any cell in your Excel worksheet and from the CDXZipStream function menu, select "Insert CDXLocateMP Function". You'll see the input box shown below:
Address information can be provided as a single line (“30 Rockefeller Plaza, New York, NY”) or in multi-line format where the street, city, state, and ZIP Code are entered separately. It is also possible to enter data according to their worksheet cell address in Excel, as shown below:
Note that address information need not be complete; enter as much information as possible and then request the missing data as desired, e.g. when looking up a ZIP code for a street address.
For reverse geocoding, data can be entered in a single line format using a pipe delimeter inserted between the latitude and longitude (e.g. "40.708972|-74.010212") or latitude and longitude can be entered separately. As with address information, worksheet cell addresses for latitude and longitude can be used in place of the actual data.
Now let’s look at the data we can retrieve:
- Best Match: The best matching address found in Microsoft MapPoint, using standard abbreviations for the street suffix, state, and country. This is always returned in single-line format, so this is also a good way of concatenating parts of an address into a single line. In the case of reverse geocoding, this represents the closest address for an inputted point of latitude and longitude.
- Latitude|Longitude: Both geocoordinates returned to a single cell, separated by a pipe delimeter.
- Street: Returns both street number and name.
- State or Region: Returns the state or region (such as Canadian province) in standard abbreviated form
- ZIP or postal code (For U.S. ZIP codes, the 5-digit code is returned. ZIP+4 is not available through CDXLocateMP. Please see information on our companion product CDXStreamer for ZIP+4 data.)
- Census tract: Assigned code for the address census tract, as defined by the U.S. Census Bureau (applies to the U.S. only)
- MSA: The name of any applicable Metropolitan Statistical Area, as defined by the Office of Management and Budget and used by the Census Bureau (applies to the U.S. only)
- Location Map: A map of the address and surrounding area will be returned as a Microsoft Excel object in the corresponding worksheet.
There is also an option to allow the use of ambiguous data. For example, if the street address of “123 Main Street “ is input, and MapPoint identifies possible locations at “123 North Main Street” and “123 South Main Street”, selecting this option will return the first instance of this address that MapPoint finds. If this option is not selected, no result is returned.
CDXLocateMP will only work in countries where Microsoft MapPoint has address find capability. For MapPoint North America, address find is available for the U.S and Canada, and for MapPoint Europe it is available for Austria, Belgium, Denmark, Finland, France, Germany, Greece, Italy, Luxembourg, Netherlands, Norway, Portugal, Spain, Sweden, Switzerland, and the United Kingdom. For both Canada and the U.K., where postal codes are alphanumeric and used in many different combinations, only a partial postal code can be returned. For more information please see the article Using CDXLocateMP to Find Postal Codes for Countries in MapPoint NA and Europe.
We also offer Microsoft Excel templates where the CDXLocateMP function is accessed programmatically, so operations like geocoding and reverse geocoding are performed automatically for the user. The quality of the address match is also returned in these templates, which is useful where precision is important; if the match is not exact, manual checking of an address for typos or other errors may be warranted. Here's a short tutorial showing how to geocode in a template:
These templates are free and can be downloaded from our links page.