For a general overview of how to insert data and functions into your worksheet, please refer to the topic Inserting Data and Functions.
CDXZip4 is a right-click function available in all versions of CDXZipStream. Using our web-based location analytics service CDXGeoData, this function can verify the accuracy of United States address data and return a USPS approved address with Zip+4. It can also import additional information such as county, county FIPs, state FIPs, and DPBC (Delivery Point Barcode) for each address. A separate API key is necessary to access CDXGeodata that is not part of the CDXZipStream license. A free API key for evaluation can be obtained here.
CDXZip4 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 CDXGeoData data requests, you enable the AutoCopy option and set the returned data to text when obtaining data for lists of locations. Autocopy is discussed in more detail below.
If an address is determined to be valid by CDXZip4, the street and address range exists. However, the address does not necessarily represent an actual business or residence.
Before using CDXZip4, you must obtain a CDXGeoData API key and enter it into CDXZipStream. Please see the topic Set CDXGeoData API Key for more information.
To use as a right-click function, right-click on any cell in a worksheet, select the Insert CDXZip4 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 mapping 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.
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 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 "fullAddressOut" option will return complete address correction for both single and multi-line entries. See below for a complete list of returned data options.
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 occurrence 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.
CDXGeoData Settings: Click on the "CDXGeoData Settings" button to edit settings for CDXGeoData API services and to view request usage for your linked account.
Please see the topics CDXGeoData Settings and Set CDXGeoData Key for more information.
For a case where you wish to apply the CDXZip4 function to a list of addresses, the following settings are recommended:
Here we are requesting that "nineDigitZipcode" 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:
Note that the returned "nineDigitZipcode" data in column E is returned as text and not formulas.
To use CDXZip4 function in a custom function formula, use the following formats:
For a single-line address:
=CDXZip4(Returned Data Option, Single-line Address)
For a multi-line address:
=CDXZip4(Returned Data Option, Street, City, State, Zip (or Postal Code), Country)
Use the values below for the "Returned Data Option" parameter:
Here is an example using a custom function formula to return "cityStateZipOut" with "4" as the Returned Data Option: