Why Excel removes leading zeros from a CSV.
And how to keep them.
A CSV file is plain text, so 00123 is stored correctly. The zeros disappear when Excel opens the CSV: it guesses each column’s type, reads 00123 as the number 123 and drops the zeros. Saving the workbook afterwards keeps the changed value. To keep them, import the column as Text, turn off Excel’s automatic data conversion, or build the XLSX straight from the original CSV so nobody has to open it in Excel first.
What actually happens
A CSV has no column types. When you double-click one, Excel applies the General format and converts anything that looks like a number, date or formula. The same guessing causes several related problems:
- Leading zeros are dropped. ZIP and postal codes, SKUs, account and employee numbers: 00123 becomes 123.
- Long numbers are rounded. Excel keeps 15 significant digits, so an 18-digit ID such as 123456789012345678 is stored as 123456789012345000 and shown as 1.23457E+17.
- Codes turn into numbers or dates. 1E10 becomes ten billion; 03-04 becomes a date.
- Text starting with = is treated as a formula.
Once the file is saved from Excel, as CSV or as XLSX, the converted value is what gets saved. Save As cannot restore digits that were changed on opening.
First, check whether your file is already damaged
Open the CSV in a plain text editor such as Notepad (Windows) or TextEdit (Mac). If you see 00123 there, the file is fine and only Excel’s view of it is wrong. If the zeros are missing in the text editor too, the CSV was already saved from a spreadsheet: get a fresh export from the source system, because no tool can know which zeros were there.
Four ways to keep leading zeros
1. Import the CSV with Text columns (Excel)
Instead of double-clicking, open a blank workbook and use Data → From Text/CSV. In the preview, set Data Type Detection to Do not detect data types, or choose Transform Data and set the ID columns to Text. Then load the data.
2. Turn off automatic data conversion (Microsoft 365)
Recent Microsoft 365 versions of Excel have File → Options → Data → Automatic data conversion. Clear the options for removing leading zeros and for converting long numbers to scientific notation. This only changes Excel on your computer; a colleague who opens the same CSV still sees the conversion.
3. Import without conversion (Google Sheets)
Use File → Import → Upload and clear Convert text to numbers, dates, and formulas before importing.
4. Create a text-typed XLSX from the original CSV
When the file goes to someone else, give them an XLSX in which every cell is already text. They can double-click it and nothing changes. CSV to Excel does this in your browser and reads the workbook back to check every cell against the CSV before download.
Which method fits
| Method | Who has to do it | Best for |
|---|---|---|
| Data → From Text/CSV | Everyone who opens the CSV | Your own analysis in Excel |
| Automatic data conversion off | Each person, once, in Microsoft 365 | People who open CSVs every day |
| Google Sheets import option | Everyone who imports the CSV | Teams working in Google Sheets |
| Text-typed XLSX | Only the sender | Handing a file to someone else |
Questions
Can I add the zeros back with a number format?
A custom format such as 00000 displays five digits, but the cell still holds the number 123. That works for fixed-length codes like US ZIP codes, not for IDs of varying length or values over 15 digits, which are already rounded.
Does putting an apostrophe in front help?
Typing '00123 into a cell stores text, which is fine for a few cells. It does not help with a CSV that is converted when it opens.
Last updated