How to Validate ZIP Codes in Excel Without an API

I once inherited a 14,000-row customer spreadsheet where about 400 ZIP codes were quietly wrong. Not blank. Not obviously broken. Just wrong: four digits where five belonged, letters smuggled in from Canadian entries, and the entire state of Massachusetts stored as numbers so 02134 had become 2134. Mail merges failed silently and the direct mail campaign reached roughly nobody on time. Everything below is the cleanup playbook I wish I'd had that week.

Step 1: Stop the bleeding with data validation

If people are still typing ZIP codes into your sheet, enforce the format at entry. Select the column, go to Data, Data Validation, Allow: Custom, and use this formula for cell A2:

=AND(LEN(A2)=5, ISNUMBER(VALUE(A2)))

This rejects anything that is not exactly five numeric characters. Add an error alert that says "Enter a 5-digit ZIP code (e.g. 90210)" so whoever types knows what happened. Format validation is cheap and it stops roughly 80 percent of new problems on day one.

Step 2: Fix the leading-zero problem

This is the single most common ZIP corruption in Excel, and it has destroyed more datasets than any typo. ZIP codes starting with 0, every code in New England, New Jersey, Puerto Rico, and the Virgin Islands, get mangled when Excel treats them as numbers. 02134 becomes 2134. 00601 becomes 601. The zero is gone and no formatting trick brings the data back.

The fix, in order of preference:

1. Store ZIPs as text. Format the column as Text before pasting or typing data. Prefix with an apostrophe ('02134) when entering by hand.
2. Repair with TEXT(). If the zeros are already gone, restore them with =TEXT(A2,"00000") in a helper column, then paste as values over the original. This only works if no more than the leading zeros were lost.
3. Pad in Power Query. For large datasets, use a custom column step: Text.PadStart(Text.From([Zip]), 5, "0"). This is the reliable, repeatable version of the TEXT() fix.

Step 3: Clean the column with Power Query

For a one-time cleanup or a recurring import, Power Query beats formulas. Load the table with Data, From Table/Range, then apply these transforms:

• Trim whitespace: Transform, Format, Trim. Leading and trailing spaces break every lookup.
• Strip non-digits: add a custom column with Text.Select([Zip], {"0".."9"}) to delete dashes, letters, and stray characters. Handle ZIP+4 separately if you need it.
• Extract the 5-digit base: Transform, Extract, First Characters, enter 5, or split on the "-" delimiter and keep the left segment.
• Filter the exceptions: filter rows where the length is not 5 into a separate table for manual review. Never silently delete rows.

Keep the original column alongside a new NormalizedZIP column so you have an audit trail. Close and Load pushes the clean column back to the workbook.

Step 4: Check existence with a free reference CSV

Format checks catch typos. They do not catch 12345-shaped fiction. For existence, download a free ZIP code CSV, put it on a second sheet, and flag every code in your data that does not appear in it:

=IF(ISNA(MATCH(A2, RefSheet!$A:$A, 0)), "NOT FOUND", "OK")

or, in newer Excel:

=IF(ISNA(XLOOKUP(A2, RefSheet!$A:$A, RefSheet!$A:$A)), "NOT FOUND", "OK")

This is a lookup, not an API call. It works offline, it works on 100,000 rows, and it catches the 99999 problem that every formula above misses.

What about ZIP+4?

The nine-digit form (90210-1234) is legitimate and common in address databases. Validate it separately: either strip it down to the 5-digit base for existence checking, or validate the full pattern as exactly 10 characters matching 5 digits, a dash, and 4 digits. Most mailing workflows only need the 5-digit base, so I normalize to 5 and keep the +4 in a separate column when it exists.

FAQs