For a general overview of how to insert data and functions into your worksheet, please refer to the topic Inserting Data and Functions.
CDXLocateMP is a right-click function available in MapPoint-compatible versions of CDXZipStreamTM. Using Microsoft MapPoint, the function can determine the validity of an address, and if valid, return latitude, longitude, street, city, state and ZIP Code, county, census tract, and MSA (Metropolitan Statistical Area) information. It can also insert location maps. For non-U.S. addresses, regional and postal code data are also available. The application also supports reverse geocoding, retrieving an address or ZIP Code from an entered latitude and longitude. CDXLocateMP can be employed as a right-click function as well as input directly into your worksheet as a custom function formula. (Please also refer to the help section on custom functions.) Direct insertion of a custom function works with all returned data except maps.
Note that if an address is determined to be valid by CDXLocateMP, the street and address range exists. However, the address does not necessarily represent an actual business or residence.
Before using CDXLocateMP, you must install Microsoft MapPoint. CDXRouteMP is compatible with the 2002 through 2013 versions of MapPoint. A free downloadable trial of Microsoft MapPoint is available here. If your version of MapPoint includes a run CD, it must be inserted in your PC drive.
MapPoint has been discontinued by Microsoft as of December 2014, although it continues to be offered by third-party software resellers.
To use as a right-click function, right-click on any cell in a worksheet, select the Insert CDXLocateMP Function, and you will see the input box shown below:
The following inputs are required:
Enter as Single-line or Multi-line address: Click on the button that describes the format of the address. A "single-line" address, as shown above, is input within the single line in the input box ("101 Field Point Road, Greenwich, CT 06830"), or within a single cell of the worksheet. In a "multi-line address" the street, city, state (region), zip (postal code), and country information have separate input boxes, or may be in separate worksheet cells.
Single-line address: Input the single-line address. Partial addresses are acceptable. You must, however, include the street name. You may also input a worksheet cell location here (such as "A1" or "$A$1") which contains the single-line address.
Reverse Geocoding is possible by entering data in the format of "Latitude | Longitude"
Multi-line address: Input all individual data that make up the address. Partial addresses are acceptable. You must, however, include the street name. You may also input a worksheet cell location here (such as "A1" or "$A$1") for each individual part of the address.
Reverse Geocoding is possible by substituting the latitude in the first (street field) and longitude in the second (city) field.
Returned Data: Specify what data will be returned to the worksheet for the given address. The "best match" is the address that best represents the given address. If no "best match" can be found, "Not found - Address is ambiguous or invalid" will be returned. When specifying "Latitude|Longitude", latitude and longitude data will be returned to the worksheet cell separated by a vertical bar "|". Use the "Best Match" function to rapidly verify mailing list addresses. Set the Ambiguous flag to 1 to allow results with two good results or more to match. You can also specify to embed a location map created with MapPoint into an Excel worksheet.
When entering either a single-line or multi-line address, either the full state name or two-letter abbreviation can be used. If the state name is included in the returned data, only the two-letter abbreviation is returned.
For reverse geocoding from latitude and longitude the CDXLocateMP function has to search in MapPoint for the nearest address. MapPoint will not return data for any arbitrary latitude, longitude combination. The program will try different latitude and longitude combinations up to a 1/2 mile radius to find a matching MapPoint valid address. If it does not find a valid address, it will return "Location Not Found".
In cases where you wish to apply the CDXLocateMP function to a list of addresses, input the custom function formula as shown below:
The CDXLocateMP function is input as a custom function formula in the following generalized form:
For a single-line address:
=CDXLocateMP(Returned Data Option, Single-line Address)
For a multi-line address:
=CDXLocateMP(Returned Data Option, Street, City, State (or Region), Zip (or Postal Code), Country)
The "Returned Data Option" setting in the case above is "1" because the latitude of the address is returned to the worksheet. The settings for this can be
For this example, the latitude for the first address is 42.75 degrees:
To apply this function to the rest of the data, copy and paste the custom function formula. In this case, we copy cell E2 and paste it to the range E3 through E8:
This cut and paste technique will also work if the addresses are listed in rows instead of columns as shown here.