Remove a US- prefix from a ZIP
Turn US-02108 into 02108, and flag any other country prefix.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | LEFT(UPPER(A8),3)="US-" | Checks the cell starts with US-, in any case. | Yes |
| 2 | MID(A8,4,10) | Keeps up to ten characters after US-. | "02108" |
| 3 | "Review country prefix" | What the cell says for any other start. | Not used for row 8 |
| 4 | IF( … ) | Uses part 2 for US-, otherwise part 3. | "02108" |
The result is text, so leading zeros like the 0 in 02108 stay.
=IF(LEFT(UPPER(A8),3)="US-",MID(A8,4,10),"Review country prefix")How to use it
Yellow bar at the top? Click Enable Editing.
- See it work. The Example tab shows 20 made-up examples with the formula already in.
- Use your own addresses. Paste ZIPs with a US- prefix into the green column of the Template tab. The ZIPs fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Use this only on a list that puts US- in front of each ZIP. It never guesses a country from the digits.
Microsoft's help: MID function
OTHER ROUTES YOU MIGHT LIKE

Restore a five-digit ZIP from a number
2108 → text 02108
Get the template →
Display a ZIP+4 from a nine-digit number
21081234 → 02108-1234
Get the template →
Join a ZIP and an optional +4 extension
02108 + 1234 → 02108-1234
Get the template →
Keep the zero in a ZIP Code like 02108
2108 → 02108
Take this route →