How to get the ZIP code from an address in Google Sheets

The ZIP code is already in the address

For an address like 418 Alder Ln, Eugene, OR 97401, no lookup is needed. This formula takes the five digits at the end, ignoring a ZIP+4 ending and a trailing “USA”:

=IFERROR(REGEXEXTRACT(A2, "(\d{5})(?:-\d{4})?(?:,\s*(?:USA|US|United States))?\s*$"), "")

It returns text, so 02109 keeps its zero. To split the street, city and state too, see our guide to splitting addresses into columns. Rows that come back empty have no ZIP code in the text; those are the ones to look up below.

Don’t guess a ZIP code from the city alone. Large cities have many, and some ZIP codes cross city and county lines.

Check and clean a ZIP code column

Before looking anything up, it’s worth knowing which ZIP codes you already have are usable. These formulas work on a ZIP code in A2:

We checked the pattern on 97401, 02109 and 97401-1234 (valid) and 2109, 9740, 974011234 and a ZIP code with a leading space (not valid). TO_TEXT matters: without it, a ZIP code stored as a number makes REGEXMATCH return an error. A four-digit result usually means a lost zero rather than a wrong code, so run the TEXT fix first, then the check. Only use the TEXT fix on a column of five-digit ZIP codes.

A valid shape doesn’t prove the ZIP code exists or matches the address. For that, look it up below, or check single addresses on the USPS ZIP Code Lookup, which also gives the ZIP+4.

1. Free, thousands: the Census batch geocoder

The U.S. Census Bureau geocoder is free, needs no account, and takes up to 10,000 US addresses per file. It returns the matched address with its ZIP code.

1. Make a tab with exactly five columns and no header row: a unique ID (1, 2, 3…), street, city, state and ZIP code. Leave ZIP empty if you don’t have it.
2. File → Download → Comma-separated values.
3. On the Census site, choose Find Locations Using… → Address Batch, upload the file and download the result.
4. Import the result with File → Import, and untick Convert text to numbers, dates, and formulas so codes like 06 keep their zeros.

Here is what it returned when we ran three addresses through it on October 27, 2026:

The ZIP code is the last five digits of the matched address (column E of the result): =IFERROR(REGEXEXTRACT(E2, "(\d{5})$"), ""). Bring it back to your list with XLOOKUP on the ID column rather than pasting side by side, so a reordered result can’t shift rows. The third row shows the limits: the Census file covers street addresses, not every private campus road, business name or PO box, so expect some No_Match rows and handle them another way.

Turn Census codes into a county name

For counties, use Find Geographies Using… → Address Batch instead. Each match gets four extra columns: state code, county code, tract and block. In our test, Mountain View came back as state 06, county 085, and New York as 36, 061.

To get the name, download the Census county code list (a pipe-separated text file; import it with the separator set to Custom: |, again without converting text to numbers) into a tab called Counties. Then:

=XLOOKUP(I2&J2, Counties!B:B&Counties!C:C, Counties!E:E, "")

where I and J are the state and county code columns. 06 + 085 is Santa Clara County; 36 + 061 is New York County (Manhattan).

2. Free, hundreds: an Apps Script

Google’s own geocoder is available inside Google Sheets through Apps Script, and it handles addresses outside the US and messier input than the Census file. This script reads the ZIP code and county from the structured parts of the result, which is more reliable than chopping up the formatted address text.

1. Choose Extensions → Apps Script, delete the sample code and paste:

/**
 * Adds a "ZIP code" menu to the spreadsheet.
 * Paste into Extensions > Apps Script, save, then reload the sheet.
 */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('ZIP code')
    .addItem('Get ZIP code and county for selected addresses', 'zipCountySelection')
    .addToUi();
}

/**
 * Select ONE column of addresses, then run ZIP code > Get ZIP code and county.
 * Writes ZIP code, County and Status into the 3 columns to the right of the
 * selection. Rows that already have a status are skipped, so you can run it
 * again after a quota stop or a timeout.
 */
function zipCountySelection() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const sel = sheet.getActiveRange();
  const addresses = sel.getValues();
  const out = sheet.getRange(sel.getRow(), sel.getColumn() + 1, sel.getNumRows(), 3);
  out.setNumberFormat('@');                                 // keep leading zeros (02109)
  const results = out.getValues();
  const geocoder = Maps.newGeocoder().setRegion('us');      // prefer US matches
  const started = Date.now();

  for (let i = 0; i < addresses.length; i++) {
    const address = String(addresses[i][0]).trim();
    if (!address || results[i][2] !== '') continue;         // blank, or already done
    if (Date.now() - started > 5 * 60 * 1000) break;        // stop before the 6-minute limit
    try {
      const res = geocoder.geocode(address);
      if (res.status === 'OK' && res.results.length) {
        const parts = res.results[0].address_components;
        const zip = part_(parts, 'postal_code');
        const county = part_(parts, 'administrative_area_level_2');
        results[i] = [zip, county, zip ? 'OK' : 'NO ZIP IN MATCH'];
      } else {
        results[i] = ['', '', res.status];                  // e.g. ZERO_RESULTS
      }
    } catch (e) {
      // Usually the daily quota: "Service invoked too many times for one day: geocode"
      out.setValues(results);
      SpreadsheetApp.getUi().alert('Stopped at row ' + (sel.getRow() + i) + ': ' + e.message);
      return;
    }
  }
  out.setValues(results);
}

/** The long_name of the first address component with the given type, or ''. */
function part_(components, type) {
  const c = (components || []).find(x => x.types.indexOf(type) !== -1);
  return c ? c.long_name : '';
}

2. Save and reload the spreadsheet. A ZIP code menu appears.
3. Make sure the three columns to the right of your addresses are empty, select the address cells, and choose ZIP code → Get ZIP code and county for selected addresses. Approve the permissions the first time; it’s your own script.

It writes ZIP code, County and a status (OK, ZERO_RESULTS, or NO ZIP IN MATCH when the address is too vague, such as just a city or state). Rows that already have a status are skipped, so after a stop you can run it again. Apps Script allows 1,000 geocode calls a day on a personal Google account and 10,000 on Google Workspace. We tested the script against stand-ins for the Sheets and Maps services before publishing.

3. An add-on: MapRows

MapRows is our add-on for address lookups. Open it with Extensions → MapRows → Open MapRows, tick your address column, choose the Standard output, and run Geocode. You get the postcode (the ZIP code in the US) alongside the coordinates, formatted address, city, state and country. The Everything output adds the county, house number and street as separate columns, plus a match confidence, so weak matches show up as LOW_CONFIDENCE instead of a quietly wrong ZIP code.

It works for addresses in any country, adds a status to every row, and can pause and resume on long lists. The results are yours to keep in the sheet.

Which to use

Common problems

• Leading zeros vanish. 02109 became 2109 because Sheets read it as a number. Format the column as Plain text, or rebuild with =TEXT(A2, "00000").
• A ZIP+4 is needed. The Census result and most geocoders return five digits. ZIP+4 codes come from the Postal Service’s own address tools.
• The ZIP code is for the wrong place. The address was ambiguous (no city or state, or a street name that exists in many towns). Add the city and state, then look it up again.
• Some rows never match. New builds, rural routes and PO boxes are the usual reasons. Try another method for just those rows.

Frequently asked questions

Related

• How to split an address into columns in Google Sheets
• How to geocode addresses in Google Sheets
• How to calculate the distance between ZIP codes in Google Sheets
• MapRows documentation
• All guides

Disclosure: we make MapRows, the Google Sheets add-on mentioned in this guide. Every formula and the script here work without it.

Google Sheets and Google Maps are trademarks of Google LLC. ZIP Code is a trademark of the United States Postal Service. MapRows and Cellbrook are not affiliated with or endorsed by Google, the U.S. Census Bureau or the Postal Service.