To extract a city, state, or zip code from a full address in Google Sheets, install the free InstaMaps add-on. Use the =POSTCODE(A2) or =CITY(A2) custom formulas to pull specific components from a messy address into their own columns, handling bulk ranges up to A2:A1000 automatically.
This workflow is built for delivery planners managing 200-home rural routes, sales teams grouping leads by county, and local councils mapping public infrastructure. Instead of manually parsing text strings, you end up with a structured, clean dataset ready for pivot tables or a live map.
- →Use =POSTCODE(A2), =CITY(A2), or =STATE(A2) to pull specific components from a full address string.
- →These functions rely on the InstaMaps add-on, which processes data via geocoding rather than brittle text-splitting logic.
- →This family covers =POSTCODE/=ZIP, =CITY/=TOWN, =COUNTY/=REGION, =STATE, and =COUNTRY.
- →Enable the add-on via Extensions > InstaMaps > Enable formulas, or insert them using the sidebar's Build-the-workflow button.
- →The free tier allows 100 lookups per day (1,000/day with a free email unlock).
Syntax and Arguments
InstaMaps provides a family of extraction functions designed to split a full address string into its core geographic components. These formulas parse standard text addresses (such as "10 Downing Street, London SW1A 2AA") or coordinate pairs to isolate specific data points like the city, state, or postal code.
Because these functions are built to operate like native Google Sheets formulas, they accept both single-cell references and vertical ranges. Passing a continuous range like A2:A1000 automatically fills the adjacent column, eliminating the need to drag fill handles or manually array-enter the formula. If an address is malformed, the function attempts a best-effort parse and returns a blank cell rather than a structural error.
The POSTCODE and ZIP functions operate identically, with POSTCODE formatted for international addresses and ZIP tailored for US-based 5 or 9-digit formats. Similarly, COUNTY and REGION act as aliases depending on the specific geographic layout of the country being parsed.
=POSTCODE(address)
=ZIP(address)
=CITY(address)
=TOWN(address)
=COUNTY(address)
=REGION(address)
=STATE(address)
=COUNTRY(address)
Worked Examples
Scenario 1: A 200-Home Real Estate Farm Filtering by County. A property developer is evaluating a 200-home farm in a mixed-region area. The raw property addresses are imported into column A, spanning A2:A201. The developer needs to isolate the county for targeted mailers and regional tax calculations. By clicking cell B2 and entering =COUNTY(A2:A201), the formula evaluates all 200 rows. The output automatically populates B2:B201 with the respective county names. The developer then highlights row 1, applies Data > Create a filter, and filters column B to show only "Surrey". This immediately segments the 200-home farm, allowing the developer to run a focused =INSTAMAP() on just the filtered rows to visualise property clusters.
Scenario 2: A 5-Crew Service Business Extracting Zip Codes. A landscaping company manages 320 active client accounts across a wide metropolitan area. The dispatcher needs to assign these accounts evenly among 5 distinct crews based strictly on geographic zones. The client addresses are pasted into Sheet1!A2:A321. The dispatcher selects C2:C321 and types =ZIP(A2:A321). The function returns the 5-digit US zip codes (e.g., "90210") down the entire column. With the zip codes isolated, the dispatcher creates a helper column using =CLOSEST_TO() to calculate the distance from each crew's home depot, ensuring no crew crosses into another crew's zip code boundary unnecessarily.
Scenario 3: A 47-Stop Delivery Route Pulling City Names. A local courier manages a dense, 47-stop delivery route. The route management software exports raw, unformatted text into the sheet, leaving inconsistent data in A2:A48. The driver needs a clean, alphabetised list of city names to cross-reference against local delivery time restrictions. By entering =CITY(A2:A48) in cell B2, the formula parses the unstructured text blocks and returns just the city names for all 47 rows. The driver can then highlight the column and apply a standard A-Z sort to group the stops by municipal boundaries.
Instead of manually typing these functions, you can insert them using the InstaMaps sidebar. Open the sidebar via Extensions > InstaMaps > Enable formulas, navigate to the specific extraction formula you need, highlight your target range, and click insert. The sidebar ensures exact syntax and provides a Build-the-workflow button that writes whole chains automatically if you need to combine this with other routing formulas.
Common Errors
When an extraction formula fails, Google Sheets displays standard error messages. Two specific issues account for nearly all parsing failures in address columns: missing add-on authorisation and daily quota limits.
A #NAME? error indicates that Google Sheets does not recognise the function name. This occurs if the InstaMaps add-on is not installed, or if it has not been authorised to run in the current spreadsheet. To resolve this, ensure the add-on is installed from the Google Workspace Marketplace, then navigate to Extensions > InstaMaps > Enable formulas to grant the script the necessary permissions. For a step-by-step walkthrough of granting permissions and fixing script authorisation issues, read our guide on resolving the [google-sheets-geocode-formula](/google-sheets-geocode-formula) #NAME? error.
Quota limit messages appear when you exceed the daily lookup allowance. The free tier permits 100 lookups per day, which increases to 1,000 lookups per day with a free email unlock. If you process a 500-row sheet while on the standard 100-lookup tier, the cells will display a quota exceeded message after the 100th row. The formula will automatically re-evaluate the remaining rows the following day, or you can complete the free email unlock to immediately raise the limit and process the rest of the column.
Composes With
InstaMaps formulas are designed to pass outputs directly into one another without manual copying or intermediate formatting. You can build automated workflows by feeding the output array of one extraction formula directly into the input of another mapping or routing function.
Chain 1: Postcode mapping with live URLs. If you manage a delivery sheet with 200 addresses in column A, you first extract the delivery zones into column B using =POSTCODE(A2:A201). Next, feed those postcodes directly into the mapping function. Place =INSTAMAP(A2:A201, B2:B201) in cell D1. The add-on reads the addresses in column A for pin locations and uses the extracted postcode array in column B for territory shading. The function returns a single hosted shareable map URL. When a dispatch clerk adds a new address to row 202, the =POSTCODE formula automatically extends, =INSTAMAP detects the new array length, and the hosted map automatically plots the new pin without requiring a manual refresh.
Chain 2: Routing by city boundaries. A regional service desk tracking 47 open tickets across the West Midlands needs to assign stops to 5 local crews. First, extract the service locations into column B using =CITY(A2:A48). Because =CITY returns cleanly structured geographic data rather than raw, inconsistent text strings, you can pass that array straight into the distance formula. In cell E2, input =SORT_BY_DISTANCE(B2:B48, E1), where E1 contains your depot's postcode. The function calculates the straight-line distance from your depot to every extracted city location and sorts the output in ascending order. You can then filter the top 9 results for your first morning crew. If you prefer viewing this route on a navigation app instead of a spreadsheet list, wrap the sorted results in =ROUTE_LINK(B2:B10) to generate a Google Maps link with the maximum 11 stops.
Limits and Honest Alternatives
You might wonder why you should use =POSTCODE(A2) or =CITY(A2) instead of Google Sheets’ built-in text functions. The honest answer is that native functions work perfectly if your data is flawlessly formatted, but fail completely on real-world address lists. You can learn more about the underlying mechanics in our google-sheets-geocode-formula guide.
The native alternative: =SPLIT(). If every row in your sheet follows a strict 'Street, City, State, Postcode' format, you can split the text using a comma delimiter. =SPLIT(A2, ",") will break the address into four separate columns instantly. This costs nothing, requires no add-ons, and does not consume your daily quota.
The limitation: Address data is rarely uniform. A typical CRM export contains formatting variations. Row 2 might read '10 Downing Street, London, SW1A 2AA'. Row 3 might read '10 Downing Street SW1A2AA'. Row 4 might have missing commas or secondary unit numbers like 'Flat 1, 10 Downing St'. =SPLIT relies entirely on consistent delimiters. If the commas are missing, the postcode ends up in the city column.
How InstaMaps handles this: InstaMaps extraction formulas do not look for commas or spaces. They send the text string to the geocoding server, which compares the string against a known spatial database. The server recognises that 'SW1A 2AA' is a postcode and 'London' is a city, regardless of where the commas sit. This requires an internet connection and counts against your free tier of 100 lookups per day (1,000/day with a free email unlock). If you have 50,000 perfectly formatted rows, use =SPLIT. If you have 200 messy exports, use structured geocoding.
Who This Is For
We recommend the InstaMaps address extraction workflow for specific operational data cleaning tasks.
Get it if you manage a local CRM and need to structure years of inconsistent manual data entry. If your sales staff types addresses differently, =CITY and =POSTCODE will format that mess into reliable, filterable columns for mail merges.
Get it if you dispatch small local fleets. A logistics dispatcher handling 47 daily stops across 5 crews needs to quickly extract postcodes and group them into geographical territories. Passing the extracted data directly to =SORT_BY_DISTANCE removes the need to manually plot addresses on a map.
Get it if you calculate regional jurisdiction fees. Field service businesses (plumbing, landscaping) use =COUNTY and =STATE to identify out-of-bounds addresses and automatically apply standard mileage fees based on broad regions rather than exact street distances.
Skip it if you are processing a one-off list of perfectly standardised addresses exported directly from a controlled e-commerce checkout form. Standardised data does not require server-side geocoding.
Skip it if you need to process data entirely offline in a secure, air-gapped environment. Because InstaMaps relies on external servers to interpret messy strings, the formulas require an active internet connection to function.
Map your Salesforce accounts in under 5 minutes — no admin setup.
Common Questions
Use the =POSTCODE(A2) formula, pointing the cell reference to the column containing your full addresses. If you have a list of 500 delivery stops in column A, paste =POSTCODE(A2) in B2 and drag it down. The add-on reads the underlying address data and returns just the postal code, ignoring the street and city information.
A #NAME? error means Google Sheets does not recognise the function, which happens when the InstaMaps add-on is not installed on the current account. You must install the add-on from the Google Workspace Marketplace to resolve this. If it is already installed, click Extensions > InstaMaps > Enable formulas, or see our name-error post for troubleshooting steps.
There is no functional difference between =POSTCODE, =ZIP, and =ZIPCODE in the add-on; they are aliases designed for regional preferences. =ZIP is commonly used for United States addresses, while =POSTCODE is standard for UK and Australian datasets. All three functions query the exact same address database and return identical results for the same cell reference.
You can split addresses by placing different extraction formulas in adjacent columns. For example, put =CITY(A2) in column B and =STATE(A2) in column C to isolate those components from a single unstructured string. For highly erratic text, wrap the original address in =CLEAN_ADDRESS(A2) first, then run your city and state extraction formulas against that cleaned output.
The free tier processes 100 lookups per day, which covers any of the address parsing formulas like =CITY or =COUNTRY. If you register your email address with the add-on, this daily quota increases to 1,000 lookups at no cost. If you need to process a 2,000-row sheet, you will need to split the work across two days or multiple accounts.
Yes, you can use =COUNTY(A2) or =REGION(A2) to isolate the administrative boundary for a list of addresses. Once you have that data in a dedicated column, you can run standard Google Sheets pivot tables to group your routes or count visits per territory. To visualise these groups, use the =INSTAMAP() function to generate a hosted, shareable map URL that colours points by region.
Stop fighting with LEFT(), RIGHT(), and REGEXMATCH functions to split text. InstaMaps isolates cities, states, and postcodes directly in your sheet. You can open the sidebar via Extensions > InstaMaps > Enable formulas, select your cells, and insert formulas without typing.
Install InstaMaps free