Why Excel removes leading zeros from your data
Excel turns 02134 into 2134 and long IDs into 1.23E+15 when it opens a CSV. Here is why it happens and how to get your data in without the damage.
You export a list of customers, open it in Excel and every ZIP code in New England has lost its first digit. 02134 is now 2134. The phone numbers that started with a zero are shorter too and the order references have turned into something like 4.00012E+15. Nothing was wrong with the file. Excel changed the data as it opened it.
Why it happens
A CSV file is plain text. It has no way to say “this column is a code, not a number”. So when Excel opens one, it looks at every value and guesses what it is. Anything made only of digits looks like a number and numbers don’t have leading zeros, so 02134 becomes the number 2134. The zero isn’t hidden. It is no longer there.
Long numbers fail a different way. Excel holds numbers with 15 significant digits, so a 16-digit value is rounded and it displays the result in scientific notation because it is too wide for the column. Values such as 3-4, 1/2 or MARCH1 are read as dates. Geneticists eventually renamed several human genes because Excel kept turning their names into dates in published data.
The damage happens when you save
While the file is open, the original CSV on disk is untouched. The moment you save it back as CSV, Excel writes its own version of every value and the zeros, the last digits of long numbers and the original text of anything it turned into a date are gone from the file too. If a system later imports that file, it gets the damaged values.
So the first rule is simple: if you opened a CSV in Excel and the data looks wrong, close it without saving and start again from the original.
Three ways to get the data in safely
Convert it to a real Excel file first. An .xlsx file, unlike a CSV, records the type of every cell. The CSV to Excel converter writes numbers as numbers only when they’re unambiguous and everything else as text, so the workbook opens with every value exactly as it appeared in the CSV. It also tells you how many values Excel would have changed.
Import instead of opening. In Excel, Data, From Text/CSV opens an import preview where you can set a column’s type to Text before loading it. That works, but it has to be done column by column, every time.
Fix the settings on your own computer. Recent Microsoft 365 versions of Excel on Windows let you switch off the automatic conversions. That protects you, but not the colleague you send the file to.
When you’re the one sending the CSV
If other people will open your export in Excel, you can protect them. Send an .xlsx instead of a CSV or send both. If it has to be CSV, keep identifiers such as ZIP codes and phone numbers as text in the system that produces it and warn the recipient. The CSV cleaner is useful for tidying the file itself, but no CSV can force Excel to leave a value alone.
For a related next step see Combine CSV files without repeating headers.
Common questions
Can I get the zeros back after saving?
Only if you know how many digits the value should have. For five-digit ZIP codes, a custom number format of 00000 displays the zeros again, but the cell still holds a number. For anything irregular, such as product codes of different lengths, the information is gone and you need the original CSV.
Why does my 16-digit number end in zeros?
Excel stores numbers with 15 significant digits of precision. A 16-digit card, account or tracking number is rounded to 15 digits and the last one becomes zero. Saved like that, the real number can’t be recovered from the file.
Does Google Sheets do the same thing?
Google Sheets also converts values on import by default, though its rules differ slightly. Its import dialog has an option to turn automatic conversion off, which keeps text as text.
Is there a setting in Excel to stop this?
Recent versions of Excel for Microsoft 365 on Windows have options under File, Options, Data, in a section called Automatic data conversion, to keep leading zeros and long numbers. Older versions and Excel on other platforms don’t and the setting only helps on the computer where it is changed.