VERIFIED SOLUTIONi




Validate Address app for Excel Support FAQ

UPDATED: 27 July 2016

The Validate Address app standardizes and validates addresses in your Excel spreadsheet using address data from postal authorities from around the world. It can correct information and format the address using the format preferred by the applicable postal authority. It also adds missing postal information, such as postal codes, city names, and state or province names.

Select a topic for help with frequently asked questions:

WHAT'S NEW?

The latest release of the Validate Address app for Excel contains the following enhancements:

  • The Validate Address app now supports automatic identification of address fields when address headers are present and the Selected data has column headings check-box is selected. The app identifies addresses on the basis of valid header names or header abbreviations. 
  • The header names are taken from leading customer relationship management (CRM) systems and data management tools. The abbreviations are user-entered header entries. For example: For the 'Firm Name' field, the valid header names and abbreviations can be 'Name', 'Firm', 'Firm Name', 'Frm', 'FrmName', 'Company Name' etc.

ABOUT VALIDATE ADDRESS APP

What is the Validate Address app?
The Validate Address app standardizes and validates addresses in your Excel spreadsheet using address data from postal authorities from around the world. The addresses in your spreadsheet are replaced with a standardized or formatted version of the address. If you have the Gold or Silver package, the Validate Address app also provides the latitude and longitude coordinates of the address.

To validate addresses, the Validate Address app analyzes your addresses and compares them to a database of known addresses. If the address is found in the database, the Validate Address app corrects any incorrect address information in your spreadsheet and formats the address using the format preferred by the applicable postal authority. It also adds missing postal information, such as postal codes, city names, state or province names, and so on. If the address is not found in the database, the app attempts to format the address lines according to the conventions of the appropriate postal authority.

Which version of Excel do I need to use the app?
You must have Excel 2013 or Excel Online to use the Validate Address app.

Does the Validate Address app work with Excel Online?
Yes, you can use the Validate Address app with Excel Online. Note that on Windows 7, the app does not work with Internet Explorer versions 9 or 10.

Is my address data secure and private?
Yes. We transport it to our address validation server using a secure connection and only hold on to the address details long enough to validate it and return it back to you. Once you see the validated result in your Excel spreadsheet, the address is no longer on our server.

How do I report problems with the app?
Send an email to software.support@pb.com describing the issue you have with the Validate Address app.

 

TRYING AND BUYING

Can I try out the app before deciding whether to buy?
Yes. To get a free trial, download the Validate Address app from the Microsoft Office Store. Once installed, click Sign Up Now and follow the instructions to obtain a free key.

How much does Validate Address cost?
There are different pricing plans depending on the number of addresses you want to validate:

Basic
750 credits per month
Address Block Formatting
US and Canada at 1 credit per address

Silver
3,000 credits per month
Address Block Formatting
Geo-coordinates
US and Canada at 1 credit per address
Rest of the world at 15 credits per address

Gold
12,000 credits per month
Address Block Formatting
Geo-coordinates
US and Canada at 1 credit per address
Rest of the world at 15 credits per address

Free
250 credits
US and Canada at 1 credit per address

Enterprise
More than 12,000 credits per month
Contact SaaSSalesSupport@pb.com for more information.

To purchase a plan, click the Sign Up Now link in the Address Validation App.

Can I change subscription plans?
Yes, you can change your subscription plan at any time by clicking the My Account link in the Validate Address app, then selecting Modify Plan. When you change plans your credit card will be refunded based on the number of days remaining in the current billing cycle.

How do I cancel my subscription?
To cancel your subscription, contact software.support@pb.com. Your credit card will be refunded based on the number of days remaining in the current billing cycle.

 

VALIDATING ADDRESSES

How do I validate an address?

  1. Enter the addresses. See the section: Do my addresses need to be in a particular format?
  2. Select the cells that contain the addresses you want to validate.
  3. If the first row of your data contains headers (column headings), check the box Selected data has column headings.
  1. Click Select Addresses.
  2. Verify that the cell range shown contains the addresses you want to validate, then click OK.
  3. If the addresses are in an Excel table, all rows of the table will be validated, including rows you do not select. If the addresses are not in an Excel table, only the rows you select will be validated.
  4. Review the default field types chosen by the application, and make any changes that are necessary.
  5. Choose whether you want your addresses to appear with all the letters capitalized (in upper case), or whether you want to just capitalize the first letter in each word.
  6. Click Validate Addresses.

Do my addresses need to be in a particular format?
Yes, there are a couple requirements for your address data. First, make sure that each cell that contains address data has a format of "Text". If a number format is used, address validation may not work. This is especially important for cells that contain postal codes since some postal codes begin with a zero and Excel will remove the leading zero if the format is not "Text", causing validation to fail.

Second, each part of the address needs to be in a separate column.

If the address field contains headers and the Selected data has column headings check-box is selected, then you need not provide addresses in a particular address format. The app automatically identifies addresses based on valid header names or header abbreviations. The valid header names are taken from various leading CRM systems and Data Management tools. The abbreviations are user-entered header entries.

For example, for the Firm Name field, valid header names and header abbreviations include 'Name', 'Firm', 'Firm Name', 'Frm', 'FrmName', and 'Company Name'. 

Refer the screens below to see unformatted input addresses with valid abbreviated headers such as 'cty' for City field, 'country/territory' for Country, and 'name' for Firm Name.

The screen below shows the auto identified address fields by the app:
 

The app returns the value None for any unidentified fields. For example: The app does not identify AdL2 address field in the screen above and returns None in the Address Line 2 field.

 

If the addresses do not contain a header, you must specify the specified address format as explained below: 

Firm Name: The name of a company or organization. For example, Pitney Bowes.

Address Line 1: The first line in an address, typically the building number and street. For example: 225 N. Michigan Ave.

Address Line 2: The second line in the address. This line typically contains information such as apartment number or suite number. For example: Suite 208.

City: The name of the municipality where the address resides for purposes of receiving mail. The city used in a mailing address may differ from the physical location of the address.

State/Province: The name or postal abbreviation for the state or province where the address is located.

Postal code: The code used by the local postal authority to facilitate mail delivery. For example, in the United States this is the ZIP Code. Postal codes may be in one of the following formats:

  • 99999
  • 99999-9999
  • A9A9A9
  • A9A 9A9
  • 9999 999

Country

The Validate Address app does not work on addresses that have more than one of these in the same column.

What happens when an address is validated?
If the Validate Address app is able to validate an address, the address in your spreadsheet is replaced with the validated address. If you want to keep the original address, save it to another spreadsheet before using the Validate Address app.

How much of an address do I need in order for address validation to work?
It depends. Some addresses can be validated with as little information as a postal code and a PO Box number. Other addresses require more information, such as a house number, street, and postal code. The more address components you can provide, the greater the likelihood that Validate Address will be able to validate the address.

Can the app validate addresses from any country?
The Validate Address app can validate addresses in nearly any country. The precision with which address can be validated varies by country. The following table lists the supported countries, grouped by the level of precision available for each country. The precision levels are:
A - At least 98% of the addresses in these countries can be validated to the street or building level.
B - Some addresses in these countries can be validated to the street or building level.
C - Addresses in these countries can only be validated to the locality or postal code level

View Precision Levels for each country.

Back to top

VALIDATION RESULTS

How do I know if an address has been validated?
When you click the Validate Address button, a message appears that lets you know how many addresses were validated and how many were not. If the address you entered was already valid and formatted according to postal standards, the address will not change. If any changes were needed to make the address valid, the address is corrected in your spreadsheet.

How do I find the latitude/longitude coordinates for an address?
If you purchased the Gold or Silver package, you can obtain the latitude and longitude coordinates of the address by selecting two extra empty columns when you select the cells containing address data:

Make sure the columns are mapped correctly:

Can I view additional information about the validation results?
Yes. You can select a column in your spreadsheet to contain information about the validation result. To do this, select an empty column in addition to the columns that contain the address data. Then, in the field mapping, choose Status for the blank column. When you validate addresses, one of the following messages is shown in the status column:

blank
If the status column is empty, it means the address was successfully validated. For U.S. addresses only, a blank value also means that the address was DPV confirmed.
View What is DVP for more information.

InsufficientInputData
The address was not validated because the address does not contain enough information to perform validation. For example, if the address contains only a street name, there is not enough address information to perform validation.

MultipleMatchesFound: Possible Multiple Addresses Found
The address was not validated because Validate Address could not identify a single address that best matches your input address. Since there is more than one address that could be the standardized, validated form of the address, Validate Address does not make any changes to the address.

UnableToDPVConfirm
The street, city, state/province, and postal code are validated, and the building number is within the range of valid building numbers for the street segment, but Validate Address could not confirm that the exact building number is correct. The address is considered validated, but not DPV confirmed. For more information, see What is DVP.
UnableToValidate: Address Not Found
The address is not valid because Validate Address could not find an address in the postal data that was a match for the address.

Does the Validate Address app verify that a particular person or business is at the address?
No. The Validate Address app only validates the address itself. It does not validate that a particular person lives at the address, or that a particular business is located at the address.

What is a block address?
A block address is the address formatted as it would appear on a physical mail piece. The block address is formatted using the standards of the applicable postal authority. For example, this is a block address for a U.S. address:

Parliament PL STE 600
Lanham MD 20706-1882
United States
 

How do I get a block address?
To get block addresses, you must have the Basic, Silver, or Gold package. When selecting the addresses in the spreadsheet, select a column for the block address:

 

Make sure the column is mapped correctly:

What is DPV?
Delivery Point Validation (DPV®) is a United States Postal Service® (USPS®) technology that validates the accuracy of address information down to the individual mailing address. By using DPV® to validate addresses, you can reduce undeliverable-as-addressed (UAA) mail, thereby reducing postage costs and other business costs associated with inaccurate address information.
Note: DPV® is only available for U.S. addresses.

Without DPV®, the address validation process only verifies that an individual address is within a range of valid addresses for the given street. For example, the USPS data indicates that the range of addresses on Maple Lane is 500 to 1000. You attempt to validate an address of 610 Maple Ln. Without DPV®, this address would appear to be valid because it is in the range of 500 to 1000. However, in reality the address 610 Maple Ln does not exist: the house numbers in this section of the street are 608, 609, 613, and 616. With DPV® processing, you would be alerted to the fact that 610 Maple Ln does not exist and you could take action to correct the address.

DPV® also provides unique address attributes to help produce more targeted mailing lists. For example, DPV can indicate if a location is vacant and can identify commercial mail receiving agencies (CMRAs) and private mail boxes.

If an address is not validated, what can I do to try and correct the address?
The Validate Address app uses information provided by the postal service, so it is very accurate. If an address is not able to be validated, you may want to contact the addressee to correct the address information. In rare circumstances, such as when the address is very new (a month old or so), the postal service may not have that new address yet.

Why is the validated address so different from the one I originally had?
The Validate Address app makes corrections to your address and also formats address elements using official postal standards established by postal authorities from around the world. As a result, the validated address may look quite different from the original address.

Here are some of the things the address validation process does, some if which may result in significant corrections to an address. Validate Address:

  • Corrects misspellings in street names, city names, and company names.
  • Standardizes address elements including street names, directionals, suffixes, and postal codes, according to standards established by the local postal authority. For example, the street "North Main Street" may be turned into "N Main ST" by the address validation process if this is the format preferred by the local postal authority.
  • Corrects errors in the postal code using data provided by the postal authority.
  • Changes the city name to the name preferred by the postal authority. In some countries, names of certain cities may have multiple forms in common use. The Validate Address app changes the city name to the form preferred by the local postal authority.