How to Extract City, State, and Zip from Address in Sheets

To extract city, state, and zip from an address in Google Sheets, install the free InstaMaps add-on. Use the formulas =CITY(A2), =STATE(A2), and =POSTCODE(A2) in adjacent columns. The add-on parses the full address and returns the exact components automatically without complex scripting.

This guide is for sales teams, real estate analysts, and logistics coordinators tired of messy text-to-columns or manually copying data. Whether you are cleaning up a CRM export or routing a local delivery fleet, you will learn how to break apart full addresses into clean columns and map them in minutes.

What you need to get started

Address parsing traditionally requires complex combinations of LEFT, RIGHT, MID, and FIND functions. This method breaks easily when addresses have missing commas or unexpected formatting. A single missing space can cause standard spreadsheet logic to return an error or the wrong data entirely. To avoid manual cleaning, you need a dedicated parsing tool.

If you are trying to figure out how to extract a city from an address in Excel, you will find that standard formulas are incredibly fragile. By moving your data to Google Sheets, you can automate this entirely using custom functions designed specifically for location data.

Start with a Google Sheet containing your addresses in a single column. Install the InstaMaps add-on from the Google Workspace Marketplace. Once installed, you can open the tool via Extensions > InstaMaps > Formulas. This sidebar inserts formulas directly into your cells without typing, allowing you to extract specific address parts instantly.

1. A Google Sheet with full addresses in a single column (e.g., Column A).

2. The free InstaMaps add-on from the Google Workspace Marketplace.

3. A free tier providing 100 lookups per day (or 1,000/day with a free email registration).

Step 1: Extract the city from an address

Once your data is ready, you can isolate the city or town into its own column. Standard spreadsheets fail here because they rely on character counts, which vary wildly between a short name and a long name. InstaMaps reads the semantic structure of the location instead.

In cell B2, type =CITY(A2) or insert it via the sidebar. If cell A2 contains '1600 Amphitheatre Parkway, Mountain View, CA 94043', the formula returns 'Mountain View' immediately. You can drag this formula down your entire column to process thousands of rows.

Step 2: Extract the state and county

Next, you can isolate the state, region, or county into a new column. This is particularly useful for organising sales territories or calculating regional taxes accurately.

To get the state, use =STATE(A2). Using the previous example, this formula will return 'CA'. You can also use =COUNTY(A2) to return the specific administrative county, or =COUNTRY(A2) if you are processing international address lists. These formulas ignore the street data and pull only the requested administrative boundaries.

Step 3: Extract the zip code

Extracting the postal code is one of the most common data cleaning tasks. Postcodes are essential for shipping calculations, demographic analysis, and mapping logistics.

In a new column, enter =POSTCODE(A2). You can also use =ZIPCODE(A2) depending on your preferred terminology. If cell A2 contains '1600 Amphitheatre Parkway, Mountain View, CA 94043', the formula will output '94043'. This works reliably whether the postcode is a standard 5-digit format or a longer international variant.

Worked example: A 200-home farming route

Consider a local irrigation business assessing a 200-home agricultural zone in rural Texas. They receive a messy spreadsheet from a local authority containing the full addresses in a single column, but they need to group the homes by town and postcode to assign work crews efficiently.

They use =CITY(A2:A201) to pull the town names into Column B, and =POSTCODE(A2:A201) to pull the postal codes into Column C. Instead of manually copying and pasting text for an hour, the formulas populate the entire column in seconds.

With the data cleaned, they use the =INSTAMAP() function to generate a live hosted shareable map URL of the 200 homes. Because the map updates automatically when the sheet changes, the dispatch team can easily track newly added properties. If they need to send a crew out, they use =ROUTE_LINK() which uses Google Maps' official URL scheme to build a navigation link with a maximum of 11 stops for a single crew's morning route.

Limits and honest alternatives

InstaMaps is a free tool, but it operates within sensible limits to manage server costs. The free tier allows 100 lookups per day. If you verify your email address, this limit increases to 1,000 lookups per day at no cost. This is plenty for most small business workflows, property lists, or sales prospecting batches.

If you need to process 50,000 rows in under a minute without registering an email, a paid enterprise API or commercial mapping software might be a better fit for your needs. However, for standard teams looking to clean address data quickly without writing scripts, the free tier is highly capable.

Common Questions