Open a CSV of zip codes in Excel and 00420 silently becomes 420. Save, and the zeros are gone from the file itself. The CSV was never wrong — Excel just decided your codes were numbers. Here's how to stop it, and what to do when it already happened.
CSV stores text; Excel guesses types — type inference. Any all-digit field gets read as a number, and numbers don't have leading zeros — 00420 and 420 are the same number, so Excel shows (and saves) 420.
The data most hurt by this is never actually numeric: zip codes, SKUs, product codes, phone numbers, employee IDs, EAN/UPC barcodes. They're labels made of digits — and they must be treated as text.
Before fixing anything, see whether the zeros are still in the CSV or already destroyed. Open the file in a viewer that doesn't coerce types — the zeros either are or aren't there.
If the file still has them, every fix below works. If a previous save already stripped them, skip to the last section.
In Excel, never double-click the CSV. Use Data → From Text/CSV, click Transform Data (or the column type icon), and set the affected columns to Text before loading. The zeros survive because Excel never gets to guess.
Google Sheets: File → Import and untick “Convert text to numbers, dates and formulas”.
The durable fix is to stop giving Excel a guessable file at all. Convert the CSV to a real .xlsx with ID-like columns already typed as text — then there's nothing to strip, for you or for the colleague you send it to.
The converter here detects ID-role columns (zips, SKUs, phone-shaped digits) and writes them as text cells.
If the values had a fixed width (US zips are 5 digits, EANs 13), the zeros are recoverable: pad every value back to that width. Re-export from the original system when you can — that's the ground truth — but when you can't, padding restores the format exactly.
Add a calculated column that re-pads the damaged one, then drop the old column. If the lengths varied (some 4-digit, some 5-digit codes sharing a column), padding can't know which were short — that data needs the source export.
Opening by double-click runs Excel's type inference; if you save (even without edits), the displayed — stripped — values are written back. View CSVs in a non-coercing viewer when you only need to look.
Yes, any spreadsheet that auto-converts types. Sheets has an import checkbox to disable conversion; the typed-.xlsx route works everywhere.
No — CSV faithfully stores 00420. The information is intact until a program interprets it as a number and saves the result.
Same family of problem: +44… can get mangled as a formula or number. Import as Text, or store phone columns quoted; the validator flags phone-shaped columns at risk.