CDXLocateBing Function
Previous  Top  Next

For a general overview of how to insert data and functions into your worksheet, please refer to the topic Inserting Data and Functions.

CDXLocateBing is a right-click function available in Bing-compatible versions of CDXZipStream™. Using the web-based mapping service Bing Maps, 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. It can also insert location maps.

CDXLocateBing 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. However, we highly recommend that to speed up data retrieval and minimize the number of Bing 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 CDXLocateBing, the street and address range exists. However, the address does not necessarily represent an actual business or residence.

Before using CDXLocateBing, you must obtain a Bing Maps license key and enter it into CDXZipStream™. Please see the topic Set Bing Maps Key for more information.

To use as a right-click function, right-click on any cell in a worksheet, select the Insert CDXLocateBing Function, and you will see the input box shown below:

clip0479

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 mappping 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 being mapped 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 Bing Maps. Location maps cannot be returned when using Autocopy.

Allow Ambiguous Data: This specifies how closely Bing matches the given location. This generally should be used for reverse geocoding (obtaining address data from a point of latitude and longitude).

Bing Maps Settings: Click on the "Bing Maps Settings" button to edit settings for Bing driving and routing calculations. Also use this interface to set the Bing Maps key required for using CDXRouteBing and CDXLocateBing functions, and to track the number of Bing data requests made for the current Excel session. Please see the topics Bing Maps Settings and Set Bing Maps Key for more information.


For a case where you wish to apply the CDXLocateBing function to a list of addresses, the following settings are recommended:

clip0438

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. AutoCopy is enabled, and the returned data is "Set to Text" which is recommended for long lists. The result is:

clip0439

Note that the returned latitude data in column E is returned as text and not formulas.

To use CDXLocateBing function in a custom function formula, use the following formats:

For a single-line address:

         =CDXLocateBing(Returned Data Option, Single-line Address)

    For a multi-line address:

         =CDXLocateBing(Returned Data Option, Street, City, State, Zip (or Postal Code), Country)

Use the values below for the "Returned Data Option" parameter:

clip0440

When obtaining a best match address by reverse-geocoding a point of latitude and longitude, make sure to check the "Allow Ambiguous " option.

Location maps cannot be returned through custom formulas or when using the AutoCopy option.

Here is an example using a custom function formula to return "Best Match" with "0" as the Returned Data Option:

clip0441