Function Problems

How to Extract a ZIP Code from an Address Using Python in Excel

Maira Mehtab Sep 28, 2026 869 views

Question details

The user wants to extract a five-digit ZIP code from a full address string (e.g., '708 West Shore Dr Richardson TX') using Python within Excel.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Parsing unformatted or semi-formatted text data to isolate US postal codes from standard address strings.
Observed behavior: The user is seeking a method to accomplish this, noting that Python in Excel relies on user-defined scripts rather than automatic address validation or native geocoding features.

Before you start

Ensure you have an active Microsoft 365 subscription with the Python in Excel feature enabled, and familiarize yourself with basic regular expressions (regex) for string manipulation.

Solution 1Recommended

Extract the ZIP Code Using the Python Regex Module

Use Python's built-in regular expression (re) module to search the address string and extract the five-digit numerical sequence.

Because address formats can vary significantly and Python in Excel does not automatically perform geocoding or address validation, regular expressions provide the most reliable method for extracting a ZIP code that is already present in the source data.

This approach requires the ZIP code to exist within the text; it will not generate or look up a missing ZIP code.

Activate Python in Excel

Select the empty cell next to your address data and type '=PY(' to activate the Python formula input box.

Import the Regex Module

In the formula bar, type 'import re' and press Enter to start a new line within the Python editor.

Define the Search Pattern

Assuming your address is in cell A1, write the script to search for exactly five digits: 'match = re.search(r"\b\d{5}\b", xl("A1"))'.

Return the Extracted Value

On the next line, type 'match.group(0) if match else "No ZIP"'. Press Ctrl+Enter to execute the Python script and extract the ZIP code into your worksheet.

Advanced Geocoding and API Support: For API development or complex Python scripts involving address validation, consult the Excel Q&A on Microsoft Learn or Python communities like Stack Overflow, as the Microsoft Community primarily assists with basic add-in functionality.

Free Microsoft Office alternative

Extract Data Easily with WPS Spreadsheet

If you don't have access to the subscription-based Python in Excel feature, WPS Office provides a lightweight, free alternative. WPS Spreadsheet offers powerful built-in text extraction functions, regular expression support, and JS Macros to parse complex address data efficiently.

1. Download and Install WPS Office: Visit the official WPS Office website to download the free, lightweight installer.
2. Open Your Spreadsheet: Launch WPS Spreadsheet and seamlessly open your existing .xlsx files without losing any formatting.
3. Utilize Extraction Functions: Use built-in text manipulation formulas or advanced JS Macros to parse your address strings and extract ZIP codes instantly.

Seamless compatibility with Microsoft Excel (.xlsx) formatsBuilt-in advanced text formulas for quick data extractionLightweight installation with zero complex setup requirementsCompletely free to use with a familiar, intuitive interface

microsoft office alternative - wps office

Frequently Asked Questions

Can Python in Excel automatically look up a ZIP code if it is missing from the address?

No. Python in Excel does not natively provide address validation or geocoding capabilities. If the ZIP code is missing from the text, you would need to write an advanced script to connect to a third-party mapping or postal API to retrieve it.

What regular expression is best for extracting standard US ZIP codes?

The standard regular expression pattern to find a 5-digit US ZIP code is '\b\d{5}\b'. The '\b' denotes word boundaries, ensuring that you only capture standalone five-digit numbers rather than parts of longer tracking numbers or street addresses.

Where can I find help for complex Python API development in Excel?

For advanced script troubleshooting and API development, it is recommended to visit the Microsoft Learn Excel Q&A forums or dedicated Python developer communities such as Stack Overflow.

Why does my Python script return an error when parsing certain addresses?

Errors often occur if an address is missing the ZIP code entirely, causing the regular expression search to return 'None'. Ensure your code includes fallback logic (like an 'if' statement) to handle cases where no match is found, preventing the formula from breaking.

Excel & Data Problems