CDXLocateHere is a right-click function available in Here-compatible versions of CDXZipStream™. Using the web-based mapping service Here (www.here.com), this function can determine the validity of an address, and if valid, return latitude, longitude, street, city, state, ZIP Code or postal code, county, and country information, as well as the confidence level in the accuracy of the data. It can reverse geocode, retrieving an address or ZIP Code from an entered latitude and longitude.
CDXLocateHere 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.) However, we highly recommend that to speed up data retrieval and minimize the number of Here data requests, you enable the AutoCopy option and set the returned data to text when obtaining data for lists of locations. Autcopy is discussed in more detail below.
If an address is determined to be valid by CDXLocateHere, the street and address range exists. However, the address does not necessarily represent an actual business or residence.
Before using CDXLocateHere, you must obtain a API key and enter it into CDXZipStream™. A free developer key can be obtained at developer.here.com. Higher usage paid keys are available at here.com.
To use as a right-click function, right-click on any cell in a worksheet, select the Insert CDXLocateHere 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 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 are in separate worksheet cells.
Single-line address: Input the single-line address. Partial addresses are acceptable. You may also input a worksheet cell location here (such as "A1" or "$A$1") which contains the single-line address. When requesting multiple locations, refer to them by their range address in Excel, e.g "A1:A18". A named range may also be used for mapping. Multiple addresses should always been in single-line format.
When using the single-line address option, 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 may also input a worksheet cell location here (such as "A1" or "$A$1") for each individual part of the address.
When using the multi-line address option, reverse geocoding is possible by entering the latitude in the first (street field) and longitude in the second (city) field.
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.
Returned Data: Specify what data will be returned to the worksheet. The "Best Match" is the address that best represents the given location. If no best match can be found, an error will be returned. Select "Best Match" to verify mailing list addresses or to reverse geocode when you need address data for points of latitude and longitude. You can also specify to embed a location map created with Bing Maps into an Excel worksheet.
AutoCopy: By enabling AutoCopy, data will automatically be returned for an entire list of locations, starting at the specified worksheet cell and ending at the first occurence of an empty row. By using the "Set To Text" option, the data is returned as text instead of custom formulas. This allows for sorting, moving or other manipulation of the returned data while avoiding inadvertent data requests to Here.
Allow Ambiguous Data: This specifies how closely Here matches the given location with a ranking of 0 to 1.
Here API Settings: Click on this button to edit settings for Here calculations and input a Here API key.
For a case where you wish to apply the CDXLocateHere function to a list of addresses, the following settings are recommended:
Here we are requesting that latitude data be returned for a list of multi-line addresses located in columns A through D and with the first address in row 2.
Note that the returned latitude data in column E is as formulas.
To use CDXLocateHere function in a custom function formula, use the following formats:
For a single-line address:
=CDXLocateHere(Returned Data Option, Single-line Address)
For a multi-line address:
=CDXLocateHere(Returned Data Option, Street, City, State, Zip (or Postal Code), Country)
Use the values below for the "Returned Data Option" parameter:
Location mapping results can also be returned as displayed below:
