It’s 3:12 PM on a Tuesday. You just got an email from Sales Ops: "Please add ZIP codes to the new lead list before EOD." You open Leads_Q3_2024.xlsx. Column A has full addresses like "1234 Pine St, Portland, OR 97205" — but also "456 Oak Ave, Suite 201, Seattle, WA" and "789 Maple Dr, Apt B, Austin, TX 78704-1234". No ZIP column. And the mailing vendor requires 5-digit ZIPs — not ZIP+4. You’ve got 2,347 rows. And your coffee’s cold.
The Setup
You’re working with Sheet1, where raw addresses sit in column A (A2:A11). No consistent formatting. Some include ZIPs. Some don’t. Some have typos. Some use abbreviations. None are standardized.
| Row | Full_Address | Expected_ZIP |
|---|---|---|
| 2 | 1234 Pine St, Portland, OR 97205 | 97205 |
| 3 | 456 Oak Ave, Suite 201, Seattle, WA | 98101 |
| 4 | 789 Maple Dr, Apt B, Austin, TX 78704-1234 | 78704 |
| 5 | 555 Cedar Ln, Boston, MA 02115 | 02115 |
| 6 | 222 Birch Blvd, #304, Chicago, IL | 60611 |
| 7 | 999 Spruce Ct, Nashville, TN 37203 | 37203 |
| 8 | 333 Redwood Rd, San Diego, CA | 92101 |
| 9 | 888 Walnut Way, Denver, CO 80202-5555 | 80202 |
| 10 | 666 Elm St, New York, NY 10001 | 10001 |
| 11 | 111 Ash Ave, Miami, FL 33132 | 33132 |
The Challenge
Excel can’t magically deduce ZIP codes from street names. It doesn’t know that "Pine St" is in Portland or that "Cedar Ln" maps to Boston’s Back Bay. You can’t use VLOOKUP unless you already have a ZIP-to-city mapping — and even then, you’d need exact city-state matches, which rarely exist in messy lead data.
Worse: FIND and RIGHT won’t help reliably. Some addresses end in ZIP+4 (like 97204-1234), some end in state abbreviations ("WA"), and some have commas after the city — but not always.
The real trap? Assuming TEXTSPLIT(A2,", ") will work. Try it on row 3: "456 Oak Ave, Suite 201, Seattle, WA" → splits into {"456 Oak Ave", "Suite 201", "Seattle", "WA"}. No ZIP at all. So now what?
Walking Through It
We’ll solve this in three layers — each one more reliable than the last.
Layer 1: Extract ZIP if already present (fast & safe)
First, assume some addresses *do* contain ZIPs. Use this formula in B2 and drag down:
=IFERROR(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"-"," "),","," ")," ",REPT(" ",100)),100)),"")
That’s a mouthful — but here’s what it does: replaces hyphens and commas with spaces, then uses SUBSTITUTE + REPT to pad spaces so RIGHT grabs the last “word” reliably. Then TRIM cleans extra spaces.
Test it on A2: "1234 Pine St, Portland, OR 97205" → returns "97205" ✅
A3: "456 Oak Ave, Suite 201, Seattle, WA" → returns "WA" ❌
So we need a fallback.
Layer 2: Fallback using Power Query (no API, no cost)
Go to Data > Get Data > From Table/Range. Make sure your address column is selected and “My table has headers” is checked. Click OK.
In Power Query Editor, select the address column. Go to Transform > Split Column > By Delimiter. Choose , and “At each occurrence”. That gives you up to 4 columns: Street, Unit/City, State, ZIP (sometimes).
Now add a custom column: =if Text.Length([Column4]) = 5 and Text.Is(Number.FromText([Column4]), Number.Type) then [Column4] else null. That checks for clean 5-digit ZIPs in the rightmost split.
If Column4 is blank or contains “WA”, try Column3: =if Text.Length([Column3]) = 2 and List.Contains({"AL","AK","AZ","AR","CA","CO","CT","DE","FL","GA","HI","ID","IL","IN","IA","KS","KY","LA","ME","MD","MA","MI","MN","MS","MO","MT","NE","NV","NH","NJ","NM","NY","NC","ND","OH","OK","OR","PA","RI","SC","SD","TN","TX","UT","VT","VA","WA","WV","WI","WY"}, [Column3]) then [Column2] else null.
This is clunky — but it works without external tools.
Layer 3: The real fix — free geocoding API (1,000 requests/day)
Go to geocod.io, sign up (free tier), and grab your API key.
In Excel, go to Data > Get Data > From Other Sources > From Web. Paste this URL (replace YOUR_KEY):https://api.geocod.io/v1.7/geocode?q=456+Oak+Ave%2C+Seattle%2C+WA&api_key=YOUR_KEY
But — you can’t paste dynamic addresses directly. Instead: In column C, build the URL:="https://api.geocod.io/v1.7/geocode?q="&SUBSTITUTE(SUBSTITUTE(A2," ","+"),",","%2C")&"&api_key=YOUR_KEY"
Then use Power Query > Advanced Editor to loop through those URLs. Or — faster — use Alt+D+D+R (Data > Refresh All) once set up.
Surprising tip: Geocod.io returns ZIP as [results]{0}[postal_code]. You don’t need JSON parsing add-ins — Power Query handles it natively when you expand the results record.
The Result
After applying Layer 3, here’s your final output in column B (B2:B11):
| Row | Full_Address | Extracted_ZIP |
|---|---|---|
| 2 | 1234 Pine St, Portland, OR 97205 | 97205 |
| 3 | 456 Oak Ave, Suite 201, Seattle, WA | 98101 |
| 4 | 789 Maple Dr, Apt B, Austin, TX 78704-1234 | 78704 |
| 5 | 555 Cedar Ln, Boston, MA 02115 | 02115 |
| 6 | 222 Birch Blvd, #304, Chicago, IL | 60611 |
| 7 | 999 Spruce Ct, Nashville, TN 37203 | 37203 |
| 8 | 333 Redwood Rd, San Diego, CA | 92101 |
| 9 | 888 Walnut Way, Denver, CO 80202-5555 | 80202 |
| 10 | 666 Elm St, New York, NY 10001 | 10001 |
| 11 | 111 Ash Ave, Miami, FL 33132 | 33132 |
What Could Go Wrong
Three mistakes I made — and saw others repeat — that wasted hours:
- Mistake #1: Using
FINDwith hardcoded positions. Example:=RIGHT(A2,5)fails on “WA” and “TX 78704-1234”. Always validate length *and* numeric type. - Mistake #2: Forgetting ZIP+4 isn’t valid for mail merge. If your system needs strict 5-digit ZIPs, use
=LEFT(B2,5)*only after confirming B2 is 5+ digits*. Otherwise, you’ll get “78704-” → “7870-”. - Mistake #3: Running geocoding on uncleaned addresses. “123 Main St, , NY” (double comma) or “Los Angeles CA” (no comma) breaks most APIs. Clean first:
=SUBSTITUTE(SUBSTITUTE(A2," "," "),", ",",")to collapse spaces and fix comma spacing.
Here’s your action plan — copy-paste ready:
| Step | What to Do | Shortcut / Formula |
|---|---|---|
| 1 | Extract existing ZIPs safely | In B2: =IFERROR(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"-"," "),","," ")," ",REPT(" ",100)),100)),"") |
| 2 | Clean address formatting | In C2: =SUBSTITUTE(SUBSTITUTE(A2," "," "),", ",",") |
| 3 | Build geocoding URL | In D2: ="https://api.geocod.io/v1.7/geocode?q="&SUBSTITUTE(SUBSTITUTE(C2," ","+"),",","%2C")&"&api_key=YOUR_KEY" |
| 4 | Refresh data connection | Press Alt+D+D+R |
