How to Populate a Cell with the ZIP Code Based on an Address in Google Sheets

Caitlin Dempsey

Updated:

If you have a concatenated field containing address information, you can set up a simple function in Google Sheets to be able to populate a separate column with ZIP code data.  This function will take the address information found in one cell and populate a second cell with the associated ZIP code.

For this tutorial, I have a simple spreadsheet that contains the first ten entries for the 2017 Fortune 1000 list.  As you can see in the screenshot below, I have the company name, location, and state.  What I want to add is a field with ZIP code information for each record.

Screenshot showing part of a Google sheets spreadsheet listing company names and 2017 ranking.

Set up the geocoding script

The first thing we want to do is to set up the geocoding function in a short script.  We will use this script to call the function for each of the cells we want to populate with the ZIP code.

To start, go to Tools –> Script editor.


Free weekly newsletter

Fill out your e-mail address to receive our newsletter!
Email:  

Screenshot showing how to access the Script Editor from the tools menu in Google Sheets.  Behind the drop down menu is a partial spreadsheet.

In the new script window that opens, paste the following (remove the default function template first):

function geo2zip(a) {
  var response=Maps.newGeocoder()
    .reverseGeocode(lat(a),long(a));
  return response.results[0].formatted_address.split(',')[2].trim().split(' ')[1];
}
function lat(pointa) {
 var response = Maps.newGeocoder()
     .geocode(pointa);
  return response.results[0].geometry.location.lat
}
function long(pointa) {
  var response = Maps.newGeocoder()
     .geocode(pointa);
  return response.results[0].geometry.location.lng
}

Click on the save icon and give the project a name.

Screenshot showing a the Script editor window in Google Sheets with code in the window.  Red arrows are pointing to parts of the script.

Now we are ready to run the function.  Click on the tab where your spreadsheet is located.  Select the first cell that you want to populate the ZIP code for.  You are going to now call the function which in the script window we named “geo2zip”.  In that cell paste =geo2zip() and in the parentheses type in the cell containing the concatenated address information.  In the example below, the first record has the full address in cell E2 so I will paste in cell F2 this function: =geo2zip(E2).

Screenshot showing a partial view of a Google Sheets screenshot with a cell highlight and a column to enter a call for a script.

Hit the return key and you should see that first selected cell now populated with the associate ZIP code.  To populate the rest of the cells, select the cell and use the cursor to drag down to the remaining cells by pulling down the little blue box that appears in the lower right corner.

Screenshot showing two side by side partial Google sheets columns. A red arrow across both screenshots.

You should see a loading… message indicating that the function is running.  Then the cells with populate one by one with the ZIP code value.  As a caution, any formatting issues with the full address value may result in errors (see the Francisco error in the screenshot below).

A screenshot of a partial Google Sheets spreadsheet showing Fortune 500 company names.

As you clean up your address data, those cells will recalculate and populate the cell with the actual ZIP code.  This function limits you to about 300 records before you will start getting an error message that the geocoding service has been invoked too many times.

A partial screenshot of a Google Sheets spreadsheet showing #ERROR! in some of the cells.

Watch the video tutorial

See Also

How to quickly add latitude and longitude coordinates to a spreadsheet using Google Sheets

Related

Photo of author
About the author
Caitlin Dempsey
Caitlin Dempsey is the editor of Geography Realm and holds a master's degree in Geography from UCLA as well as a Master of Library and Information Science (MLIS) from SJSU.