Intelligent City, State, and Zip in Excel

So you’re building a spreadsheet that requires the user to enter a ZipCode, City, and State… How do you build a smarter spreadsheet that automatically fills in the City and State after the user inputs only the ZipCode?

I’ve been looking at some basic tutorials that web devolopers are using with XMLHttpRequest, like this one, and was inspired to recreate the same functionality of this excellent XMLHttpRequest tutorial in Excel (scroll down the link for a demo).

Success! I’ve put together a quick flash movie to demonstrate keying a zip and automatically returning the city and state in Excel.

And here is the spreadsheet download to kick around (there isn’t that much code).

How it works

1. You need a database that has the zipcode/state/city information in it. Get that here (I went with MYSQL as demonstrated).

2. Return the city and state based on a zip code query from the Database, I used PHP to return text.

3. Back in Excel: When the ZipCode field changes, the Worksheet_Change event fires, sending the request via WinHTTP to the PHP page, which returns the City and State for the Request back to Excel.

4. I use the Split() function to parse the string and write the results to the spreadsheet.

Notes

Why not return XML?

It is only a two word string, and text is easy to deal with when there are only two words. XML may have been snazzier however because I could have just mapped the two result fields to cells, and called a custom Import URL: each time.

Why not just connect to the DB directly?

Yep that’s possible, however I’d prefer to create a “service”, this way any app I make, on any platform, can use it.

Why not just import the Zips to Excel and do a lookup?

Possible, however if multiple workbooks use the table, a change needs to be made to all of them.

The Zips in the example may be outdated.

Only tested in XL2003.