# How to extract the ZIP code from an address in Excel

When the ZIP code is the last word, take the text after the last space: =TEXTAFTER(A2," ",-1). It keeps ZIP+4 codes whole.

**Excel:** `=TEXTAFTER(A2," ",-1)` (Microsoft 365 only)

**Google Sheets:** `=REGEXEXTRACT(A2,"\d{5}(?:-\d{4})?$")`

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Address | ZIP |
| 1420 Lake St, Minneapolis, MN 55401 |  |
| 88 Oak Ave, Saint Paul, MN 55102-1234 |  |
| 7 Elm Ct, Madison, WI 53703 |  |
| 305 River Rd, Fargo, ND 58102 |  |
| 9 Harbor Rd, Boston, MA 02134 |  |

Formula in B2, filled to B6: `=TEXTAFTER(A2," ",-1)`

Result: 55401; 55102-1234; 53703; 58102; 02134

## How it works

In a US address the ZIP code comes last, so TEXTAFTER with instance `-1` returns it whether it has 5 digits or the ZIP+4 form. The result is text, which keeps the leading zero in "02134".

In Google Sheets, the pattern matches 5 digits, optionally followed by a dash and 4 more, at the end of the text.

## Other ways

- `=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))`: Works in every version: the last word, using the SUBSTITUTE and REPT trick.
- `=RIGHT(A2,5)`: The last 5 characters. Fine only when every ZIP code has exactly 5 digits. (Returns "-1234" for the ZIP+4 address.)

## Common mistakes

- Turning the result into a number drops leading zeros: "02134" becomes 2134. Keep ZIP codes as text.
- A trailing space or a country name after the ZIP code breaks the "last word" rule. TRIM first, or use the pattern version.

## Questions

**How do I keep only the 5-digit ZIP?**

Take the first 5 characters: `=LEFT(TEXTAFTER(A2," ",-1),5)`.

**How do I get the state?**

Take the last part after the comma, then the text before the space: `=TEXTBEFORE(TEXTAFTER(A2,", ",-1)," ")`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/extract-zip-code-from-address