Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
Related to @ChrisF's answer, what region are you in? –  JMD Jan 6 '10 at 16:28
    
I didn't realize that the term "ZIP" applied outside the US. Anyway, I've specified now. –  Isaac Moses Jan 6 '10 at 19:14
add comment

10 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
Yep, I do this for a living - converting data from one format to another. As mentioned, some cities can have several zip codes, for that matter, the World Trade Center buildings had their own zip codes so city/state isn't enough. The USPS has a super massive database that can do this, but it requires the street address as well and is relatively expensive. –  Blackbeagle Jan 6 '10 at 20:07
    
Could you not use the US census data from 2000? I believe that it's free for use. –  Chris Jan 6 '10 at 22:11
1  
I am not familiar with it, but even so you're talking about a massive quantitiy of data and it's not something you are going to be able to readily do in an Excel spreadsheet. –  BBlake Jan 7 '10 at 12:36
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

I've developed an addin for Excel which does forward/reverse geocoding and GCD with GoogleMaps. Freeware: http://www.calvert.ch/geodesix/

share|improve this answer
1  
would like to see the underlying code for this –  datatoo Nov 1 '10 at 14:16
add comment

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 2-5 minutes depending upon the size. The total cost, which also depends on the size, is usually about $5 to $10. If you're a non-profit or an educational institution, you can receive free address verification.

share|improve this answer
add comment

SQL Server can be configured to do that kind of think. I bet its possible to create a SQL Server web service instance WSDL that responds with GIS data. Then, maybe you could get the spreadhseet to use some VBScript to talk to it? Just a very difficult thought. ;-)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.