Why Excel Drops Leading Zeros in Postal Codes
Excel can interpret a postal code such as 02108 as a number and display 2108. The zero disappears during numeric interpretation, even when the CSV originally contained it. Import the postal column as text before that conversion, then verify the saved and re-exported values.
This guide is for people moving address data between CSV, spreadsheets and application imports. It separates a file-export defect from spreadsheet type inference, using a reproducible AddressLab fixture.
Check the raw CSV before changing Excel formatting
Open a copy of the source file in a text editor. If it contains 02108, the exporter retained the zero. If it already contains 2108, changing Excel settings cannot establish what the original code was. Keep the untouched source file so you can locate the first step that changed it.
| Raw source | Imported value | Next step |
|---|---|---|
| 02108 | 2108 | Reimport the original with the postal column typed as text. |
| 2108 | 2108 | Inspect the upstream export or original record; do not assume the missing digit. |
| 02108 | 02108 | Re-export and inspect the output file, not only the worksheet display. |
A CSV field written as "02108" is quoted for CSV parsing. Those quotes do not assign an Excel cell type. RFC 4180 describes field quoting and escaping, rather than a workbook-style type system.
Import postal codes as text before numeric conversion
Microsoft’s leading-zero guidance explains text conversion and Power Query import. On desktop editions with the relevant controls, use this approach; menu names and availability vary by version and platform:
- Use Data → From Text/CSV and select the original file.
- Open the transformation editor rather than loading inferred numeric columns immediately.
- Inspect the applied steps. Remove or edit an automatic numeric type conversion affecting the postal column, then set that column to Text while its source characters are intact.
- Load the result. Confirm both the visible code and the value used by subsequent export or transformations.
Changing a column to Text after an earlier step has converted 02108 to 2108 only turns 2108 into text. It cannot restore the discarded zero. If your edition exposes the legacy Text Import Wizard, specify a text column there instead. These Excel instructions are documentation-based; we did not operate a desktop Excel session for this guide.
Can a custom format or TEXT formula repair the code?
A custom number format can show padding while the underlying value remains numeric. That is a display decision, so validate what your next export actually writes. Postal identifiers are usually better represented as text throughout the pipeline.
If you have independently confirmed that every affected row is a five-digit US ZIP, =TEXT(A2,"00000") can reconstruct a five-character text representation. It applies a known width; it does not recover historical evidence. Do not apply that rule to mixed-country rows, ZIP+4, Canadian letters or Japanese seven-digit values.
When the country, width or original value is unknown, return to the source system. Guessing padding can turn a detectable defect into a plausible but incorrect identifier.
A reproducible export check: Boston, MA, 02108
On November 4, 2026 we reran the same fixture-generation and serialization functions used by the Test Data Generator: US, one row, boundary mode, seed csv-leading-zero-2026-10-22, rule version 2026-09-07.2, fields city, region, postalCode and country. The output was:
"city","region","postalCode","country"
"Boston","MA","02108","United States"The JSON output also retained "postalCode": "02108". This confirms the current local serialization retained the source string; it does not demonstrate how your installed Excel edition will interpret a double-clicked CSV.
Download the CSV · Download the JSON · Download the recipe. Set these options manually in the tool; recipe-file import is not currently supported.
Verify all four stages of the round trip
- Read the original file as text and record the exact postal string.
- Confirm the imported worksheet value, not only a padded display.
- Export a new file and inspect the raw postal field again.
- Have the downstream importer read it as a string and compare with the source.
Include a leading-zero code, a spaced alphanumeric code and an empty Hong Kong postal field as distinct cases. Do not use a single numeric conversion for all of them. Finally, keeping every character intact still does not establish whether the address is deliverable.
How this guide was checked
AddressLab prepared this English adaptation with AI assistance and checked the linked primary documentation and current local QA rules on November 4, 2026. The original Chinese guide is available here. Examples are synthetic fixtures or explicitly illustrative inputs. Local tests do not contact postal databases, card issuers or recipients. Report a discrepancy with the input, expected result and supporting reference through our contact page.