Can you convert an address to a zip code in a spreadsheet?

Question

Given a column of street addresses with city and state but no zip in a spreadsheet, I'd like to put a formula in a second column that yields the ZIP code. Do you know a way to do this?

I'm dealing with US addresses, but answers pertaining to other countries are interesting, too.

UPDATE: I guess I'm mostly hoping that there's a way to do this in Google Spreadsheets. I realize that you need to access a vast ZIP code database to do this, but it seems to me that such a database is already inside Google Maps. If I put an address in there without ZIP code, I get back an address with ZIP code. If Maps can do that lookup, maybe there's a way to make it happen in Spreadsheets, too.

Answer 1

Google spreadsheets has a series of functions for external data. If you can find (or create) a site which does the lookup by passing parameters, you could put a formula similar to this in:

Cell A1 (Address): 123 Main St
Cell B1 (City): Springfield
Cell C1 (State): MO

Cell D1 (combined address): =concatenate(A1,B1,C1)

Cell E1 (imported zip code): 
   =importData(concatenate("http://zipfinder.com/search?addr=",D1))

This is a hypothetical idea. The concatenate, importData and other functions for external data exist in Google Spreadsheets. "zipfinder.com" does not exist. There are lots of sites which will help you find a zip code from an address. The tricky part is finding one which accepts address data in the URL and returns something simple enough for Google Spreadsheets to use.

Answer 2

You can write a custom Apps Script function with the Google Maps service, using a similar approach to this answer. To do this, open Tools...Script editor... and paste this function in:

function getZIP(address) {
  var geo = Maps.newGeocoder().geocode(address);
  var resultComponents = geo.results[0].address_components;
  for (var i = 0; i < resultComponents.length; i++) {
     if (resultComponents[i].types.indexOf('postal_code') > -1) {
       return resultComponents[i].long_name;
    }
  }
}

You can then call this in a spreadsheet cell, for example, it correctly returns 20500 for the White House:

=getZIP("1600 Pennsylvania Ave. NW, Washington, DC")

Unlike some other approaches, it also works for non-US locations, e.g. it correctly returns 75007 for the Eiffel Tower:

=getZIP("Champ de Mars, 5 Avenue Anatole France, Paris, France")

Answer 3

There won't be anything built into a spreadsheet as verified address databases are very expensive.

You might find an online service that will do this for you. For example in the UK the Royal Mail website allows you to look up postcodes (the UK equivalent to zip codes), but you are limited to 15 searches a day. This is for personal use. Businesses will get more - but you have to sign up and pay for that.

Similar services will exist in other countries.

Answer 4

It's hard to do in Excel. You would need to call a Web Service using SOAP toolkit, or use Visual Studio. One must wonder what Microsoft is thinking.

It's much easier to do in a Google Docs Spreadsheet, then export to Excel:

You can use GeoCoder.ca's XML web service to look up the zip code by address. In Google Docs, you would use this function:

=importXML("http://geocoder.ca/?geoit=xml&showpostal=1&locate=" & A2,"//postal")

(where A2 is the street address.)

You can also get the latitude and longitude this way:

=importXML("http://geocoder.ca/?geoit=xml&showpostal=1&locate=" & A2,"//geodata")

Note that GeoCoder has a throttle on requests per day for their free service.

Answer 5

Yes - You can now find the Zip Codes in your Google Docs sheet using the following URL:

Step 1: In this landing page, enter the address which you would like to search.

Step 2: I searched for the "1245 5TH ST" address in Washington, DC and the URL looked like this:

https://tools.usps.com/go/ZipLookupResultsAction!input.action?resultMode=0&companyName=&address1=1245+5TH+ST&address2=&city=Washington&state=DC&urbanCode=&postalCode=&zip=

Step 3: Copy and paste the following formula and replace the CELL, CITY, STATE:

=Mid(importHTML(CONCATENATE("https://tools.usps.com/go/ZipLookupResultsAction!input.action?resultMode=0&companyName=&address1=",CELL,"&address2=&city=CITY&state=STATE&urbanCode=&postalCode=&zip="),"list",15),Find("DC",importHTML(CONCATENATE("https://tools.usps.com/go/ZipLookupResultsAction!input.action?resultMode=0&companyName=&address1=",CELL,"&address2=&city=CITY&state=STATE&urbanCode=&postalCode=&zip="),"list",15))+2,11)

Remember that the URL in Step 3 should match the one from Step 2. The Cell will be dynamic based on your target Cell Value.

The function in Step 3 extracts 11 characters after the STATE value. Hence, you will have to tweak the MID function so that you get the correct values. This worked perfectly for me.

One last thing: Google allows only 50 Import functions per sheet. So, with the above method you can find only 25 Zip Codes in one go.

Answer 6

You do need some sort of external address lookup tool or service. With just City/State, there is often no way to determine the correct zip code as many cities have multiple zip codes and many cities have streets which span multiple zip codes so you can't even add just the street and use a city/state/zip formula or lookup. If you need to find zip codes for addresses, you're gonna have to have to break down and pay for a service or tool. There's no good way around that.

Answer 7

In Canada the Canada Post web site allows searching of postal codes by address (and I'm not aware of any daily limit) but that doesn't help you with integration within your spreadsheet, unfortunately. I'm interested in seeing potential solutions for this as well. I've always used manual postal code lookup for the ones I don't know.

Answer 8

There are a few online services that will process an Excel spreadsheet (or CSV/tab-delimited file) and append missing ZIP Codes. The price can range anywhere from a few dollars to $100+ depending upon the service provider, some of which have setup fees, processing fees, and rush-order fees.

Some service providers can get the processed file back to you within a few minutes while others which claim "rush processing" can take 3+ business days. Based upon your question, you don't want to invest in purchasing software and plunking down thousands of dollars for an on-site software solution. If that's the case, an online provider is your best bet.

In the interest of full disclosure, I'm the founder of SmartyStreets. We offer fully automated, online address verification. You can drag and drop your Excel file on our website and we'll have it processed and back to you within about a minute or two depending upon the size.

Answer 9

In the UK you would need access to the postcode address file (PAF) which lists all the addresses in the country. Direct access to the PAF costs money, and not an insignificant amount either. We pay something like £200 per seat, per year for unlimited access to updated PAF data, but then we are throwing upwards of 10k requests at it every month.

Most other territories are similar in this regard so I'd imagine you are not going to find a legitimate way of accessing zip data in the way you wish to.

Even if you could find a site that would allow RESTful querying of their data to insert a zip directly into a spreadsheet, they would no doubt implement strict limits on the number of queries that could be made in any set period.