CDXLocateMP is a right-click function available with both the basic and premium data feeds. Using Microsoft MapPoint®, the function can determine the validity of an address, and if valid, return latitude, longitude, street, city, state and zip code information. For non-U.S. addresses, regional and postal code data are also available. 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.)
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. CDXLocateMP is compatible with the 2002 through 2009 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.
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.
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.
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 "|".
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.
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.