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

"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."

"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:

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(""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(""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."

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.

"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."