Extract city, state and ZIP or postcode from addresses in Google Sheets™

To group an address list by city, state or postcode, you need those values in separate columns. If every address has the same comma-separated structure, Sheets’ SPLIT function can do it. For addresses with different structures, a geocoder can look up the place and return its address components. Choose based on whether you need the original text or Google’s description of a place.

For consistently delimited text: SPLIT

With this text in A2:

77 Massachusetts Ave, Cambridge, Massachusetts, 02139, United States

Enter this in B2, with B2:F2 empty:

=ARRAYFORMULA(TRIM(SPLIT(A2, ",", FALSE, FALSE)))

The comma-separated pieces spread across five cells. TRIM removes surrounding spaces; the last FALSE retains empty pieces, so a missing component does not shift the following columns. Google documents the SPLIT options here.

This is text splitting: an extra comma in an apartment or building name creates another column. The same formula cannot identify the city reliably if each row uses a different address structure. Keep the original column, and check postcodes after splitting: in this example, Sheets turns 02139 into 2139. For an international list, do not assume every postal code has five digits.

For a list of places: GeoFill’s free sidebar

GeoFill is the add-on I make. It uses Google’s geocoder to return city, state/region, postcode and country, with 100 address lookups a day free. The sidebar needs no API key or paid plan.

1. Put full addresses in column A of a fresh tab, with Address in A1 and one address per row.
2. Install GeoFill from the Marketplace.
3. Open Extensions > GeoFill > Geocode addresses and choose Addresses to coordinates.
4. Select the Address column. Under Fill in, select City, Region, Postcode and Country. Untick Latitude, Longitude and Full address if you do not need them.
5. Click Geocode. The components and a Match column are filled beside your address list.

These two public addresses returned the following components in a real Sheet:

• 77 Massachusetts Ave, Cambridge, MA 02139, USA
• 10 Downing Street, London, SW1A 2AA, UK

Region is a full name, such as Massachusetts, rather than the postal abbreviation MA. It is a first-level administrative region, which means England in the second example, not a county.

For a formula, GeoFill Pro supports:

=GEOCODE(A2, "city,region,postcode,country,match")

It fills five cells in that order; leave room for the result. Formulas require Pro: $9 once per Google account. They need a successful purchase check in each spreadsheet at least every 30 days; open GeoFill and use Refresh geocoding formulas if asked. Existing subscriptions and trials keep their terms. The free sidebar fills plain values and can carry on after its daily cap the next day.

Keep leading zeros in ZIP and postcode results

The sidebar writes postcode results as plain text, so 02139 stays 02139, including when it reuses an existing output column. Keep that field as text when copying or importing it elsewhere.

If an older result already shows 2139, changing its format to plain text does not restore the missing zero. Look up the postcode again from the original address:

1. Reopen GeoFill on that tab and check the selected address columns.
2. Under Fill in, select Postcode and untick the other result fields.
3. Under More options, tick Redo rows that already have a Match.
4. Click Geocode and check the returned postcode and Match.

Redo applies to all eligible rows in the tab, including completed ones, and uses your normal allowance. Your selected source columns stay as they are. Without Redo, completed rows are skipped.

Check the returned place

A lookup can normalize the spelling, choose a different nearby place, or return no postcode. It does not simply extract the characters already in your cell. Inspect the Match and, when needed, include Full address to compare the returned place with the original. Approximate, partial and Not found results need attention. Even an Exact label describes the geocoder’s location precision; it does not certify that mail can be delivered there. Google explains its address components and match precision.

Another free option

InstaMaps documents CITY, STATE and POSTCODE formulas, with 100 lookups/day free or 1,000/day after free email registration. Its documented state output uses abbreviations such as CA. It is a capable alternative if that formula workflow fits your list.

Need coordinates too? See addresses to latitude and longitude. For the opposite direction, see coordinates to addresses.