Spreadsheet Practice

How to extract the ZIP code from an address in Excel

Excel formula

=TEXTAFTER(A2," ",-1)

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

Result on the example
one result per row in B2, filled down to B6
Works in
Microsoft 365 only
Google Sheets
Different formula, see below

See it work

The formula is already in B2, filled down to B6. Change any input and the result updates, or edit the formula to experiment.

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.

=TEXTAFTER(A2," ",-1)

Result"55401"

  1. A2"1420 Lake St, Minneapolis, MN 55401"

    The value in A2.

  2. -1-1

    Makes the value negative.

  3. TEXTAFTER(A2," ",-1)"55401"

    TEXTAFTER: Text after a delimiter.

    • textA2"1420 Lake St, Minneapolis, MN 55401"
    • delimiter" "
    • instance_num-1-1

In Google Sheets

Google Sheets needs a different formula: =REGEXEXTRACT(A2,"\d{5}(?:-\d{4})?$"). On the same data it gives the same result. Here it is running in Google Sheets mode.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))55401, 55102-1234, 53703…Works in every version: the last word, using the SUBSTITUTE and REPT trick.
=RIGHT(A2,5)55401, -1234, 53703…Different resultThe 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.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Extract the ZIP code from an address

In B2, extract the ZIP code (the last word of the address) and fill down to B6.

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)," ").

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.