ZIP or Postal Code Lookup Using Bing Maps and Excel

Our Microsoft Excel add-in CDXZipStream now has a new function, CDXLocateBing, that can find ZIP or postal codes for addresses listed in an Excel worksheet.  CDXLocateBing uses the web service Bing Maps, running in the background, as the source of address data.  Bing Maps has some level of geographic coverage for every country in the world, with postal code lookup available for many developed countries.  For the latest information on Bing Maps coverage, please refer to the Microsoft website.  Postal code retrieval is generally available when geocoding precision is either “Address” or “Rooftop”, which applies to about 70 countries.

To find a postal code for a list of addresses in Excel, right-click in an empty cell next to the list, and select the function CDXLocate Bing.  Input the worksheet cell location of the first address, and select “ZIP (Postal Code) as the returned data, as shown below

Enabling the Autocopy option will obtain data for the entire list of addresses until the first occurrence of an empty cell.  Using the “Set to Text” option will return the data as text instead of CDXLocateBing formulas, which is recommended when working with long lists of data.

When address data is in separate columns for street, city, and state, you can use the multi-line address option which will allow separate input of worksheet cell locations for each part of the address.  Whether a single-line or multi-line address is used, however, it is also a good idea to request that CDXLocateBing return the entire address (“Best match”)as well as just postal code, to both ensure Bing Maps identified the correct address and to obtain the full address in standard format.   You can see how this is done in the following video:

In almost all cases, CDXLocateBing will return the complete postal code, with the known exception being for locations in the UK where only partial codes are available.  Note that postal codes are available in their entirety for Canada, despite the use of alphanumeric codes similar to those in the UK. 

When using CDXLocateBing, complete address information is not required, although the more information provided, the more likely it is Bing will correctly identify an address.  The use of country data is generally recommended  although not required in cases where an otherwise complete address is provided for a U.S. location.   Also keep in mind that Bing does not have the ability to verify the existence of a specific house number; so long as a street number (e.g. 100 Main Street) is within a valid, allowable range for that street, it will return a valid postal code even though the address may not physically exist.

Add comment