Excel address to zip code: extracting postcodes using Google Sheets

To convert an Excel address to a zip code, import your spreadsheet into Google Sheets and install the InstaMaps add-on. Applying =POSTCODE(A2:A200) to your address column extracts postal codes directly into neighbouring cells, bypassing complex Excel macros or custom VBA scripts entirely.

This workflow is built for regional delivery planners managing 200-home routes, local government staff auditing district boundaries, or direct mail operations handling messy CRM exports. Instead of manually copying postal codes or troubleshooting formula errors, you end up with a clean, geocoded dataset and a shareable live map.

The Excel address problem and the Sheets fix

Text formulas in Excel, such as =RIGHT(A2, 5) or =MID(A2, FIND(" ", A2), 5), fail to reliably extract postcodes from address lists. Address data is inherently inconsistent. A standard 5-digit US zip code frequently expands into a 9-digit zip-plus-four format (e.g., 10024-1234), causing basic RIGHT formulas to pull "-1234" instead of the intended base code. UK postcodes present an even larger target for text-formula failure, as their lengths vary wildly (e.g., "M1 1AA" vs "EC1V 9LB"). Trailing whitespace, missing commas, and inconsistent capitalisation further corrupt rigid string manipulation attempts.

Instead of writing complex, nested arrays of TRIM, SUBSTITUTE, and LEFT functions, the technically sound fix is migrating the worksheet into Google Sheets to run dedicated location parsing. Excel lacks native geocoding or location-parsing capabilities without relying on complex VBA macros or Power Query. If you have a spreadsheet with 2,000 rows of mixed domestic and international addresses, trying to predict where a postcode begins and ends using character positions is impossible.

Google Sheets supports custom functions that query address databases rather than relying on character counts. By importing your existing .xlsx file into Sheets, you replace 40 characters of brittle Excel text formulas with a single deterministic lookup formula. This method processes batches of thousands of rows, completely bypassing the text-parsing errors that plague standard spreadsheet formatting. The import process takes seconds and requires no data loss, leaving your raw text intact while providing precise, column-based extraction.

Step-by-step: Importing your Excel file to Sheets

Moving an .xlsx file into Google Sheets requires only a few clicks, and your formatting remains identical. Before running any formulas, verify that your addresses sit in a single column for the most reliable batch processing.

1. Open Google Sheets in your browser and click Blank to start a new workspace.

2. Navigate to File > Import > Upload.

3. Drag your target file (for example, customer_database.xlsx) into the upload window.

4. Under Import location, select Replace spreadsheet to populate the new workspace completely.

5. Click Import data. Ensure your address headers sit in row 1 and data begins in row 2.

6. Click Extensions > Add-ons > Get add-ons.

7. Search for InstaMaps and click Install.

8. Once installed, open the tool by navigating to Extensions > InstaMaps > Enable formulas.

Extracting postcodes with =POSTCODE()

Once your data is in Google Sheets, you can extract the exact postal codes using the =POSTCODE() function. This formula queries the specific address string to isolate the recognised postal code, ignoring surrounding street names or region identifiers.

In cell B1, type Postcode. In cell B2, enter the following formula:

=POSTCODE(A2:A50)

This single entry automatically expands down the column, evaluating the 49 address strings in rows 2 through 50. The add-on identifies the postal segment regardless of whether the string reads "1600 Amphitheatre Parkway, Mountain View, CA 94043" or "10 Downing Street, London SW1A 2AA". It returns the exact postcode in the corresponding row. If you have 500 rows of data, you simply change the range to =POSTCODE(A2:A501).

If a cell in your selected range is entirely blank, or if the string contains only a business name without a valid address (like "John's Bakery"), the formula returns an #N/A error. To keep your spreadsheet clean and flag missing entries for manual review, wrap the function in IFERROR():

=IFERROR(POSTCODE(A2:A50), "Address Error")

The free tier permits 100 lookups per day. If you process a batch of 150 cells, the first 100 return a postcode, while the remaining 50 return a daily quota error. You can raise this limit to 1,000 lookups per day by using the free email unlock feature in the add-on settings. For spreadsheets exceeding 1,000 rows, you must process the data in increments (e.g., evaluating A2:A1001 on day one, then A1002:A2001 on day two). Alternatively, the sidebar's Build-the-workflow button handles writing these exact chained formulas for you.

Extracting cities with =CITY()

With postcodes successfully extracted into column B, the next logical step is isolating the municipality into column C. The =CITY() function works identically to the postcode extractor, querying the full string to return the specific town or city.

In cell C1, type City. In cell C2, enter:

=CITY(A2:A50)

The formula reads the identical address range and splits the specific geographic component into its respective column. If cell A2 contains "350 Fifth Avenue, New York, NY 10118", the formula returns "New York". If cell A12 contains "221B Baker Street, London NW1 6XE", it returns "London".

Because =CITY() uses database lookups rather than counting commas, it successfully parses both domestic and international addresses simultaneously. It avoids the common Excel failure of misclassifying a street name as a municipality when a user omits a comma. You can populate columns B and C at the exact same time. Enter =POSTCODE(A2:A50) in B2 and =CITY(A2:A50) in C2. Both formulas evaluate the identical address range, meaning the add-on queries the addresses to split the components into their distinct columns.

If a specific address lacks a recognised city in the database, the =CITY() formula returns an #N/A error. This instantly highlights incomplete records. By combining the outputs of =CITY() and =POSTCODE(), you transform a messy column of text strings into structured location data. Once parsed, you can pass the original addresses to =INSTAMAP(A2:A50) to generate a live hosted shareable map URL that updates immediately when you add new rows or change an address in column A.

Worked example: A 200-home farm delivery route

A regional farm box operation needs to map 200 active subscriber homes across 5 delivery crews. You have exported the subscriber list from Excel as a `.csv` or `.xlsx` file. Open Google Drive, import the file into Google Sheets, and open the InstaMaps sidebar via `Extensions > InstaMaps > Enable formulas`.

Your raw subscriber addresses sit in column A (`A2:A201`), and the assigned crew sits in column D. You need to isolate the 47 stops for Crew 3. In cell F2, type `=FILTER(A2:A201, D2:D201="Crew 3")`. This outputs 47 specific addresses into the range `F2:F48`.

Next, extract the postal data to verify the drops. In cell G2, type `=POSTCODE(F2:F48)`. In cell H2, type `=CITY(F2:F48)`. These arrays instantly populate the 47 rows with verified postcodes and towns. If an address is malformed-such as a missing street number-the postcode cell returns an error or blank result, highlighting exactly which subscriber you must contact.

To visualise these 47 stops on a single route, generate a hosted map. In cell I2, enter `=INSTAMAP(F2:F48, G2:G48)`. The formula outputs a live, shareable URL. Because the map reads directly from these cell ranges, correcting a typo in column F instantly updates the hosted map link. You can repeat this filter and map process for the remaining four crews, creating five distinct hosted URLs for your drivers.

Instead of typing these formulas manually, highlight your 47 filtered addresses in column F, open the sidebar, and click the Build-the-workflow button. The add-on writes the chains automatically, handling the row counts so you do not have to adjust cell references manually.

Limits and honest alternatives

The InstaMaps add-on is free to use, but the server costs of geocoding require daily limits. The free tier allows 100 lookups per day. If you verify your account via a free email unlock, this limit increases to 1,000 lookups per day. Extracting a postcode with `=POSTCODE()` or a city with `=CITY()` counts as one lookup against this daily quota. If your farm delivery list grows to 1,500 rows, you must process it across two days or split the sheet.

The standard alternative for an "excel address to zip code" task is writing an Excel VBA macro. This requires triggering an XMLHTTP request to a geocoding API, parsing the JSON response, and handling rate limits via `Application.Wait`. VBA does not natively parse JSON, forcing you to import a third-party script or write custom dictionary loops. If the API changes its response structure, your macro breaks.

InstaMaps handles the API negotiation, JSON parsing, and error handling on the server side. When an address fails, it returns a specific error string in the cell rather than crashing your spreadsheet. For users who need to validate 200 rows of addresses without maintaining code, the Google Sheets import path is structurally more reliable than maintaining legacy VBA scripts.

Who this workflow is for

This import-and-extract workflow is strictly designed for three specific user profiles who manage location data in spreadsheets.

Local delivery managers: If you manage a fleet of 5 to 10 vehicles, you likely track stops in an Excel sheet but lack dedicated routing software. By importing your sheet and running `=POSTCODE(A2:A201)` alongside `=INSTAMAP()`, you generate shareable map URLs for each driver. You can visually distribute workloads based on exact postcode sectors rather than guessing neighbourhood boundaries.

Direct mail marketers: Preparing bulk postal drops requires strict postcode formatting to achieve postage discounts. If your CRM export contains messy, concatenated address strings in a single column, `=POSTCODE()` isolates the mail routing code from the street address. You can quickly identify and filter out rows with missing data before sending a list to a print house, preventing returned mail.

CRM administrators: When migrating data between systems, address fields frequently become mangled. A CRM administrator can dump 500 messy rows into Google Sheets, run `=CITY()` to extract the town, and verify the data against standard formatting. If you need to map these CRM territories visually, combining the `=FILTER` function with `=INSTAMAP()` generates dynamic views based on town or postcode.

This workflow is not for enterprise logistics teams running real-time GPS tracking or processing 50,000 rows daily. You can download pre-built sheets for these specific data-cleaning tasks at get-instamaps.com/templates, allowing you to paste your raw Excel data directly into a tested framework.

Common Questions