The article’s solution or answer has been confirmed in practice by a client or Pitney Bowes agent.

Validate Address App for Google Sheets

UPDATED: 29 August 2017

The Validate Address app standardizes and validates addresses in your Google Sheets 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:

About Validate Address

What is the Validate Address app?
The Validate Address app standardizes and validates addresses in your Google Sheets using address data from postal authorities from around the world. The addresses in your Google Sheets are replaced with a standardized or formatted version 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 Google Sheets 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/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 the Google Sheets do I need to use the app?
You must use the latest GSuite to use the Validate Address app.

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 Google Sheets, the address is no longer on our server.

How do I report problems with the app?
Send an email to 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 GSuite Marketplace . Once installed, click Sign Up 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 & Canada at 1 credit per address

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

Gold 12,000 credits per month
Address Block Formatting
US & Canada at 1 credit per address
Rest of word at 15 credits per address

250 credits
US & Canada at 1 credit per address

More than 12,000 credits per month
Contact for more information

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

Can I change subscription plans?
Yes, you can change your subscription plan at any time by clicking the MyAccount link in the Validate Address app, then clicking 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 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. The addresses can be in a particular address format or can be unformatted. 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 a Spreadsheet table, all rows of the table will be validated, including rows you do not select. If the addresses are not in a Spreadsheet 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 Spreadsheet 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.

When Header row is present,different column headers need to be mapped to appropriate fields manually.

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

Address Format:

  • Firm Name: The name of a business 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 country code or name, in any of the following formats:
    • Two-character ISO 3116-1 Alpha-2 country code (For a list of country codes, see Can the app validate addresses from any country?)
    • Three-character ISO 3116-1 Alpha-3 country code (For a list of country codes, see Can the app validate addresses from any country?)
    • English country name
    • French country name
    • German country name
    • Spanish country name

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 Google Sheets is replaced with the validated address. If you want to keep the original address, save it to another Google Sheets 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 chance 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 Google Sheets.

Can I view additional information about the validation results?
Yes. You can select a column in your Google Sheets 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:

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.

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.

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 Google Sheets, select a column for the block address:

Make sure the column is mapped correctly:

Back to top

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.
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 very rare circumstances, such as if 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.

Back to top

Domain Installation

An Administrator of Google accounts for an organization can distribute the Address Validation App usage rights to all, some, or none of the users. Administrator can grant access to individual user accounts or to particular sub organization.

How it Works
Administrator installs the app on the Administrator account from the G Suite Marketplace. Once installed, the Administrator simply add users or suborgs who will be using the app. The app is automatically pushed for the selected users and these users can see and use the app in their spreadsheet under Add-ons menu. The Administrator shares the key with the users, which is required to access the app. Administrator can similarly revoke access rights from the sub organization, as needed.

It is important to know that the account balance of the subscription is debited each time when the members use the Address Validation App. All the users, who use the app for address validation, affect the billing of the subscription collectively.