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.
Tutorial: Adding ZIP code information to a spreadsheet
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.
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.
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.
Run the function to add ZIP codes to a spreadsheet
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).
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.
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).
Clean up address data to better match ZIP codes
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.
Watch the video tutorial on how to geocode ZIP codes using an address field
Tutorial: How to add latitude and longitude coordinates in Google Sheets
How to quickly add latitude and longitude coordinates to a spreadsheet using Google Sheets
