AddressBench
Verified 2026-11-22

Why Excel Strips Leading Zeros from ZIP Codes (and How UTF-8 BOM + Formula Quoting Fixes It)

Whenever a postal column is stored as an integer or opened raw in Excel, 02108 silently becomes 2108 and breaks shipping labels. Learn how to preserve leading zeros end to end.

1. The Silent Integer-Cast Bug in Order Exports

If a customer orders from Boston, MA (02108), Newark, NJ (07102), or San Juan, PR (00901), a standard CSV cell containing 02108 is parsed by Microsoft Excel as the decimal number 2108. When an operations analyst saves the sheet and uploads it to a carrier portal, USPS or UPS rejects the shipment for an invalid 4-digit ZIP code.

The same defect affects international exports: Rome (00187) turns into 187, Singapore CBD (049315) loses a digit, and Gangnam, Seoul (06164) fails Korea Post validation.

Raw CSV vs. Excel-Safe Formula Quoting
# Broken when double-clicked in Excel:
fullName,cityEn,regionCode,postalCode
Claire Caldwell,Boston,MA,02108
Samuel Mercer,Manchester,NH,03101

# Excel-safe UTF-8 BOM CSV (AddressBench default):
fullName,cityEn,regionCode,postalCode
Claire Caldwell,Boston,MA,"=""02108"""
Samuel Mercer,Manchester,NH,"=""03101"""

2. Why UTF-8 BOM Matters Alongside ="02108"

On Windows and macOS, Excel defaults to legacy ANSI/Shift-JIS code pages when opening a bare UTF-8 CSV without a Byte Order Mark (EF BB BF). Prepending \uFEFF ensures both leading-zero formulas and Japanese/Traditional Chinese/European diacritics (東京都, 臺北市, München) render cleanly on double-click.

3. Backend Schema & API Rules

Keep postalCode strictly typed as string in JSON schemas, OpenAPI specs, TypeScript interfaces, and SQL migrations. Never pass postal values through parseInt() or Number() before checking regex compliance.

Test live in the workbench Synthetic Format Specimen · Not Verified for Mail Delivery · Checked 2026-11-22
Test live in the workbench →

Frequently Asked Questions

Does ="02108" break automated CSV parsers like Python csv or pandas?

If a file is strictly for machine ingestion, prefer JSON or pass excelSafe=0 on the AddressBench Edge API (/api/generate?format=csv&excelSafe=0). Use Excel-safe quoting for human-opened spreadsheets.

Which US territories start with double zeros (00)?

Puerto Rico (006xx–007xx, 009xx) and the US Virgin Islands (008xx) both begin with 00, making them high-value regression tests.

All Guides