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.
# 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.