Cellbrook

Google Sheets guide · Locations

How to calculate the distance between ZIP codes in Google Sheets

To get the distance between two ZIP codes you need a coordinate for each one. You don’t need an API for that: the US Census Bureau publishes a free file with a center point for every ZIP code area. Import it once, and one formula gives you the miles or kilometres between any two ZIP codes in Google Sheets™.

Add MapRows to Google SheetsFree to install from the Google Workspace Marketplace

Updated · 7 min read · By the Cellbrook team

Get the free ZIP coordinates

The Census Bureau’s Gazetteer files include one for ZIP Code Tabulation Areas (ZCTAs), the Census’ versions of ZIP code areas. The 2025 file lists 33,791 of them, each with an internal point’s latitude and longitude. It’s public data: free, with no sign-up.

  1. Download 2025_Gaz_zcta_national.zip (about 1 MB).
  2. Unzip it. Inside is 2025_Gaz_zcta_national.txt, a text file with values separated by the | character.

Import them into your sheet

  1. In your spreadsheet choose File → Import → Upload and select the .txt file.
  2. Set Import location to Insert new sheet(s).
  3. Set Separator type to Custom and type |.
  4. Untick “Convert text to numbers, dates, and formulas”. This keeps ZIP codes such as 02108 (Boston) and 00601 (Puerto Rico) from losing their leading zero and no longer matching.
  5. Click Import data, then rename the new tab to ZCTA.

The tab has the ZIP code in column A, latitude in column G (INTPTLAT) and longitude in column H (INTPTLONG). You can delete columns B to F if you like, but then change G and H in the formula to C and D.

The distance formula

With the starting ZIP code in A2 and the destination ZIP code in B2, put this in C2 and copy it down:

=LET(a, XMATCH(LEFT(TEXT(A2, "00000"), 5), ZCTA!$A$2:$A),
 b, XMATCH(LEFT(TEXT(B2, "00000"), 5), ZCTA!$A$2:$A),
 lat_a, VALUE(INDEX(ZCTA!$G$2:$G, a)), lng_a, VALUE(INDEX(ZCTA!$H$2:$H, a)),
 lat_b, VALUE(INDEX(ZCTA!$G$2:$G, b)), lng_b, VALUE(INDEX(ZCTA!$H$2:$H, b)),
 IFERROR(ROUND(2*3958.8*ASIN(SQRT(SIN(RADIANS(lat_b-lat_a)/2)^2+COS(RADIANS(lat_a))*COS(RADIANS(lat_b))*SIN(RADIANS(lng_b-lng_a)/2)^2)), 1), "ZIP not found"))

What each part does:

  • TEXT(A2, "00000") puts back a leading zero that Sheets removed when someone typed 02108 as a number, and leaves ZIP codes stored as text alone. LEFT(…, 5) drops a ZIP+4 ending, so 02108-1234 works too.
  • XMATCH finds the ZIP code’s row in the Census data, and INDEX reads its latitude and longitude. VALUE turns them into numbers, since we imported everything as text.
  • The last line is the haversine formula for straight-line distance in miles. For kilometres, change 3958.8 to 6371.

The formula breaks across several lines for reading here; you can paste it as it is.

Check the results

These values come from the 2025 file and the formula above. Use them to check your setup:

FromToMilesKilometres
10001 (New York)90210 (Beverly Hills)2,453.43,948.4
10001 (New York)60601 (Chicago)710.61,143.6
02108 (Boston)10001 (New York)187.8302.2
94103 (San Francisco)95014 (Cupertino)36.859.3

If you get “ZIP not found” for 02108, the leading zero was lost on import: delete the ZCTA tab and import again with conversion unticked.

ZIP codes within a radius

To list every ZIP code within 25 miles of the one in E1, first look up its coordinates once, in F1 and G1:

=VALUE(XLOOKUP(TEXT(E1, "00000"), ZCTA!$A$2:$A, ZCTA!$G$2:$G))
=VALUE(XLOOKUP(TEXT(E1, "00000"), ZCTA!$A$2:$A, ZCTA!$H$2:$H))

Then in an empty column:

=ARRAYFORMULA(LET(z, ZCTA!$A$2:$A33792, la, VALUE(ZCTA!$G$2:$G33792), ln, VALUE(ZCTA!$H$2:$H33792),
 d, 2*3958.8*ASIN(SQRT(SIN(RADIANS(la-$F$1)/2)^2+COS(RADIANS($F$1))*COS(RADIANS(la))*SIN(RADIANS(ln-$G$1)/2)^2)),
 SORT(FILTER({z, ROUND(d, 1)}, d<=25), 2, TRUE)))

It returns the ZIP codes and their distances, nearest first. Row 33792 is the last row of the 2025 file (33,791 ZIP codes plus the header). It reads all 33,791 rows, so give it a moment.

Counting, nearest and driving distance

Count the ZIP codes within the radius. Keep the same LET as the radius formula and replace its last line, so it returns a number instead of a list:

=ARRAYFORMULA(LET(la, VALUE(ZCTA!$G$2:$G33792), ln, VALUE(ZCTA!$H$2:$H33792),
 d, 2*3958.8*ASIN(SQRT(SIN(RADIANS(la-$F$1)/2)^2+COS(RADIANS($F$1))*COS(RADIANS(la))*SIN(RADIANS(ln-$G$1)/2)^2)),
 SUM(IF(d<=25, 1, 0))))

The ZIP code in E1 counts itself, at 0 miles.

Nearest store for each customer ZIP code. Look up each ZIP code’s latitude and longitude into two columns with the XLOOKUP formulas above (one for column G, one for H), do the same for your stores, then use the formulas in how to find the nearest location in Google Sheets. They work on any pair of coordinate lists.

Driving miles between ZIP codes. The Census file can only give straight lines. Google Maps accepts a ZIP code as a place, so the Apps Script function in our driving distance guide can take TEXT(A2, "00000")&", USA" as its start and end. The result is the drive between the points Google picks for each ZIP code, usually near the middle of town, so treat it as an estimate.

Where ZIP distances go wrong

  • It’s center to center. Each ZIP code is reduced to one point. Two addresses in big rural ZIP codes can be much closer or farther apart than their ZIP codes suggest. For exact addresses, geocode the full address instead (see how to geocode in Google Sheets).
  • Some ZIP codes aren’t in the file. ZCTAs cover ZIP codes where people live, so ZIP codes used only for PO boxes or a single building or organisation are missing. For example, 10118 (the Empire State Building) and 20500 (the White House) aren’t in the 2025 file. The formula says “ZIP not found” rather than guessing.
  • It’s a straight line. Road distance is usually 20% to 40% longer. For driving miles, see driving distance in Google Sheets.
  • US only. For postal codes in other countries, geocode them with the country added, such as SW1A 1AA, UK.

Troubleshooting

  • Every row says “ZIP not found”. The formula can’t read the Census tab. Check that the tab is named exactly ZCTA, and that its columns weren’t moved: the 2025 file’s header is GEOID | GEOIDFQ | ALAND | AWATER | ALAND_SQMI | AWATER_SQMI | INTPTLAT | INTPTLONG, so A is the ZIP code, G is latitude and H is longitude. If column A lost its leading zeros (1001 instead of 01001), re-import it as text, as the import steps describe.
  • Only some rows say “ZIP not found”. Those ZIP codes aren’t in the file (see the caveats above) or have a typo. Check them with the USPS ZIP Code Lookup.
  • Formula parse error. If you copied the distance formula from this page before October 28, 2026, it used the names la1 and ln1, which Sheets reads as cell addresses (column LA, row 1) and rejects inside LET. The version above uses lat_a and lng_a instead. In locales that use semicolons between arguments, swap the commas too.
  • The radius formula is slow. It compares one point with 33,791 rows on every edit. Keep it on its own tab and, when you have the answer, copy it and Paste special → Values only.
  • The file has no city names. The Gazetteer file lists ZIP code areas, land area and centre points only. To get a city, county and state for a ZIP code, geocode it instead.

Frequently asked questions

Can Google Sheets calculate distance between ZIP codes without an API?

Yes. Import the free Census ZCTA Gazetteer file, which has a latitude and longitude for every ZIP code area, then look up both ZIP codes and use the haversine formula.

Why does my ZIP code lookup fail for ZIP codes starting with 0?

Google Sheets turned 02108 into the number 2108. Import the Census file with "Convert text to numbers" unticked, and wrap your own ZIP codes in TEXT(A2, "00000").

How accurate is ZIP code distance?

It measures between the center points of two ZIP code areas, in a straight line. That is fine for estimates, territories and rough radiuses, but it can be several miles off for specific addresses.

Why isn't my ZIP code in the Census file?

The file lists ZIP Code Tabulation Areas, built from where people live. ZIP codes used only for PO boxes, single buildings or organisations, such as 20500 for the White House, have no ZCTA.

Is the Census ZIP code file free to use?

Yes. It is public data published by the US Census Bureau, free to download with no account.

How do I count how many ZIP codes are within 25 miles?

Use the radius formula from this guide but replace its last line with a SUM that counts the distances of 25 miles or less. The guide has the full formula. The count includes the starting ZIP code itself.

Why does the ZIP distance formula show a parse error?

Names inside LET can't look like cell addresses, so names such as la1 or ln2 are rejected. Use names like lat_a and lng_a, as the formula in this guide does. Also check that your locale uses commas between arguments.

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

Google Sheets is a trademark of Google LLC. MapRows and Cellbrook are not affiliated with or endorsed by Google. ZIP Code is a trademark of the United States Postal Service.