Put the Leading Zeros Back in Your CSV Columns

Why Excel deletes leading zeros in the first place

A CSV file is plain text — nothing in it says "the zip column is text, keep it exactly as written." When you double-click a CSV, Excel parses every cell and guesses its type. A cell containing only digits becomes a number, and to a number parser 00501 and 501 are the same value. Excel stores 501, and the zeros are gone before the spreadsheet finishes opening — no warning, no green triangle, nothing to undo.

The surprise is that this isn't a display glitch: Excel never stored the zeros at all. The moment the file is saved (even clicking "Keep Current Format" on the prompt while closing), the stripped values are written back into the CSV, and that copy is permanently damaged.

The usual victims

• US Northeast ZIP codes. Everything from 00501 to 09999 starts with a zero — all of New England, New Jersey, and Puerto Rico. The lowest ZIP in the system, 00501, belongs to the IRS service center in Holtsville, NY; Excel renders it as 501, a code no carrier accepts.
• GTIN-8 / EAN-8 barcodes. These short barcodes routinely begin with 0; stripped, the check digit no longer validates.
• Phone numbers. National formats across the UK and most of Europe start with 0 — 02079460018 becomes 2079460018 and dials nowhere.
• Employee and payroll IDs. 000042 collapses to 42 and stops matching the HR system it came from.
• Bank routing codes. ABA routing numbers are exactly nine digits, and every bank in the Boston and New York Federal Reserve districts starts with 01 or 02 — 011000015 becomes the invalid 11000015.

How the repair works: pick columns, set widths

Paste your data or drop the file, then click Analyze my CSV. The "Restore leading zeros" repair is pre-selected on this page, and a table appears listing every column in your file. Tick the ones that lost zeros and type the width each value should be padded to:

Padding uses plain string operations — no number parsing anywhere, so a 13-digit EAN can't be re-damaged in transit. Two safety rules apply: only cells made entirely of digits are touched (a ZIP+4 like 08544-0291 is left alone), and values already at or above the target width never change. The preview highlights each repaired cell for a spot-check before you download.

You supply the width because the file no longer records how many zeros were removed — 501 could have been 0501 or 00501. Fixed-width code systems make the repair deterministic: a US ZIP is always five digits, an EAN-13 always thirteen.

Prevention: import as Text — and why the 00000 format trick isn't enough

Never double-click a CSV that contains code columns. In modern Excel, use Data → From Text/CSV, click Transform Data, and set the ZIP/SKU column type to Text before loading. In older versions, rename the file to .txt, open it, and in step 3 of the Text Import Wizard mark each code column as Text. Either way Excel stores the literal string 00501, and the zeros are safe.

You will also see the advice to apply Format Cells → Custom → 00000. Know what that actually does: it makes 501 display as 00501, but the cell still holds the number 501 — the format is cosmetic. Formulas compare stored values (=A2="00501" returns FALSE), a VLOOKUP against a real text ZIP column finds nothing, and anything that reads values instead of display strings — Power Query merges, database connectors, copy-paste — receives the broken three-digit code. Even when a CSV export writes out the padded display string, the next double-click strips the zeros all over again. Masking the symptom is not storing the right data; import as Text and the problem is actually gone.

Where this bites hardest

Shipping teams meet it first: label APIs and USPS address validation reject a four-digit ZIP outright, so every order bound for New Jersey, Massachusetts, or Puerto Rico stalls until someone re-keys the codes. Marketplace sellers meet it as silent SKU drift — the feed says 71589, the catalog says 0071589, and the upload either errors with "SKU not found" or quietly creates a duplicate listing that splits inventory. Payroll and banking imports fail checksum validation when a routing number arrives a digit short.

For a full walkthrough of the Text-import steps in every Excel version, read the companion guide: why leading zeros disappear from CSV files — and how to stop it.

Excel rarely stops at one column. If barcodes in the same file came out as 8.85909E+11, run the scientific notation fixer; if accented names turned into é garbage, the broken characters fixer reverses the encoding damage. Both use the same paste-and-download flow.

Frequently asked questions