You open a CSV in Excel and every ZIP code that started with a zero is now a digit shorter: 02134 became 2134. Phone numbers lose their leading 0, product codes lose padding, and if you save the file, the zeros are gone for good.
Short answer: don’t double-click the CSV. Import it with Data → From Text/CSV and set the affected columns to Text. In recent versions of Excel for Microsoft 365 you can also switch off the automatic conversion once, in Excel’s options.
Why Excel removes the zeros
A CSV file is plain text. It has no column types, so 02134 is just five characters. When Excel opens the file directly, it guesses a type for every cell, decides 02134 looks like a number and stores it as the number 2134. A number has no leading zeros, so they disappear.
The damage becomes permanent when you save: Excel writes back what it now holds, so the file on disk says 2134.
Columns that are usually hit:
- US ZIP codes in the Northeast (Boston
02134, New Jersey07030) - Phone numbers with a trunk prefix or country code (
020 7946 0018,0044…) - Product codes, SKUs, employee and customer IDs padded with zeros (
000123) - Bank sort codes and account numbers
Fix 1: import the CSV instead of opening it (Excel)
- Open a blank workbook.
- Go to Data → From Text/CSV and pick your file.
- In the preview window, click Transform Data (not Load).
- In Power Query, click the header of each column that must keep its zeros, then set Data Type → Text. Choose Replace current if asked.
- Click Close & Load. The zeros stay.
Prefer the classic wizard? Enable it in File → Options → Data → Show legacy data import wizards, then use Data → Get Data → Legacy Wizards → From Text (Legacy) and set the column format to Text in step 3.
Fix 2: turn off automatic conversion (Excel for Microsoft 365)
Recent versions of Excel for Microsoft 365 let you stop the conversion for every file you open. Go to File → Options → Data → Automatic data conversion and uncheck Remove leading zeros and convert to number format. If you don’t see this section, your Excel version doesn’t have it yet: use Fix 1.
Google Sheets and LibreOffice
- Google Sheets: File → Import → Upload, then set Convert text to numbers, dates, and formulas to No.
- LibreOffice Calc: in the Text Import dialog that appears when you open the CSV, select the column in the preview and set Column type to Text.
If the zeros are already gone
If every value in the column has a fixed length, you can rebuild the zeros. For 5-digit ZIP codes in column A, use:
=TEXT(A2, "00000")Copy the result, paste it back as values, and import the file the safe way next time (Excel will strip the zeros again if you reopen the saved CSV by double-clicking). When the length varies, as with phone numbers or IDs, the original value can’t be known: export the file again from the source system.
Sending a CSV to someone who will open it in Excel?
You can’t force Excel to keep zeros from inside a CSV. Tricks like writing ="02134" work in Excel but leave junk in the data for every other tool that reads the file. If the person will only use Excel, send an .xlsx with the column formatted as text. Otherwise send the CSV and tell them to import it as shown above.
Spot the problem before you open the file
CSVCheck flags every column where values start with a zero, so you know which columns to import as text before Excel touches them. It runs in your browser and never uploads the file.