Excel drops leading zeros because a CSV file has no column types, so Excel guesses, and anything that looks like a number becomes one. 00123 turns into 123, phone numbers lose their first 0, and long ID numbers get rounded to 15 digits. To stop it, either turn off the conversion in Excel’s options, import the file with the column set to Text, or convert the CSV to an Excel file that already stores those columns as text.
What goes wrong
| In the CSV | After opening in Excel |
|---|---|
00123 (product code) | 123 |
07700 900123 (UK mobile) | stays text only because of the space; 07700900123 becomes 7700900123 |
02134 (US ZIP code) | 2134 |
4111111111111111 (16-digit number) | 4.11111E+15, and the last digit becomes 0 |
The last row is the nastiest. Excel stores numbers to 15 significant digits, so the sixteenth digit is replaced with a zero for good. Save the file and the original number is gone.
Fix 1: change Excel’s settings (Microsoft 365 and Excel 2024)
Newer versions of Excel let you switch off the conversions. On Windows go to File › Options › Data; on a Mac go to Excel › Preferences › Edit. Under Automatic Data Conversion, untick:
- Remove leading zeros and convert to number
- Keep the first 15 digits of long numbers and display in scientific notation if required
Microsoft documents both in Set automatic data conversions. The settings apply to every workbook you open from then on, so they’re the best fix if you handle codes and IDs all the time. Older versions of Excel don’t have them.
Fix 2: import instead of opening
Instead of double-clicking the CSV, open a blank workbook and use Data › From Text/CSV. In the preview, choose Transform Data, select the columns that hold codes, and set their type to Text. Then Close & Load.
This works in any recent Excel, but you have to do it every time, for every column.
Fix 3: convert the CSV to an Excel file first
The JFDI file converter reads each value for what it is. Codes such as 00123, phone numbers and postcodes stay exactly as written, and numbers longer than 15 digits are kept as text with every digit. Real numbers, dates and percentages still become numbers and dates, so you can sort and calculate with them.
- Open the file converter and drop in your CSV (or a whole folder of them).
- Choose Excel under Convert to.
- Press Convert, then Download.
The workbook also gets a bold header row that stays put when you scroll, filter buttons and sized columns. Nothing is uploaded: the conversion runs in your browser, which matters when the file is a customer list.
Fix 4: Google Sheets
In Google Sheets, use File › Import › Upload, and untick Convert text to numbers, dates, and formulas before importing. Leading zeros and long numbers stay as written.
Already lost the zeros?
If the file has been saved after Excel removed them, the zeros are gone from the file. For codes with a fixed length you can add them back with a formula, for example =TEXT(A2,"00000") for five-digit ZIP codes. Long numbers rounded to 15 digits can’t be recovered; go back to the original export.
Why it matters
Lost leading zeros cause quiet failures: postcodes that don’t match, FIPS county codes that leave Alabama off a map, product codes that don’t find their stock. The same habit of guessing types is behind Excel turning text into dates, which famously corrupted gene names in published research. That’s the subject of why dates change when you open a CSV.