Why Excel Deletes the Zeros From Your Product Codes
Published
Excel deletes the leading zeros from a product code like 00123 because, when it opens a CSV, it decides the value is a number, and the number 00123 is 123. The zeros are not hidden by formatting; they are gone from the cell. If you save the file again, they are gone from the CSV too.
Why does Excel remove leading zeros from a CSV?
Excel removes leading zeros from a CSV because a CSV has no column types: every value is plain text, and Excel converts anything that looks like a number into a number when it opens the file. A number has no leading zeros, so 00123 is stored as 123, 01000 as 1000, and 0612345678 as 612345678.
Excel makes the same kind of guess for long digit strings. Excel keeps 15 significant digits, so a 16-digit card or account number such as 1234567890123456 is stored as 1234567890123450 and displayed as 1.23457E+15. The last digit is lost, not rounded for display.
Which data loses its leading zeros in Excel?
Any identifier written with digits loses its leading zeros in Excel: product codes, postal codes, phone numbers, account numbers and employee IDs. These values are made of digits but they are labels, not quantities. Nobody adds two postcodes together, and 00123 and 123 are different products.
| Data | In the CSV | After Excel opens it | What goes wrong |
|---|---|---|---|
| Product code (SKU) | 00123 | 123 | Lookups against the stock system stop matching |
| French postcode | 01000 | 1000 | Not a valid postcode; mail merges and shipping labels break |
| US ZIP code | 02134 | 2134 | Every New England ZIP code starting with 0 loses a digit |
| Phone number | 0612345678 | 612345678 | The number can no longer be dialled from the sheet |
| 16-digit account number | 1234567890123456 | 1.23457E+15 | The 16th digit becomes 0 |
How do I keep leading zeros when opening a CSV in Excel?
To keep leading zeros when opening a CSV in Excel, import the file through Data → From Text/CSV and set the column to Text before loading it; double-clicking the file always converts the column to numbers. The steps below use the Power Query import in Excel for Microsoft 365, Excel 2021 and Excel 2019.
- Open a blank workbook. On the Data tab, click From Text/CSV and select the file.
- In the preview, set Data Type Detection to Do not detect data types, then click Load. Every column arrives as text and every zero is kept.
- To keep other columns numeric, click Transform Data instead of Load, select only the code columns, choose Data Type → Text, and click Close & Load.
Recent versions of Excel for Microsoft 365 also have a setting under File → Options → Data → Automatic Data Conversion, where you can clear "Remove leading zeros and convert to a number". That setting changes how Excel opens every CSV on that computer, and it does nothing for a colleague who opens the same file on theirs.
Typing an apostrophe before the value ('00123) or formatting the cells as Text only helps for values you type in after the column is formatted. Neither brings back zeros Excel has already removed.
How does AnyToExcel keep leading zeros?
AnyToExcel keeps leading zeros because its type detection never treats a value that starts with 0 followed by another digit as a number, and a column is only made numeric when every value in it is a number. One value like 00123 in the first 200 rows of a column is enough to keep the whole column as text, so every code in it is written to the .xlsx exactly as it appears in the CSV.
The AnyToExcel type detection follows these rules, taken from the code that runs on every conversion:
- A value starting with
0and then another digit, such as00123,01000or0612345678, is never a number.0,0.5and0,5are numbers. - A value with more than 15 digits is never a number, so long account and card numbers keep every digit.
- Each column is tested on its first 200 non-empty values. The column is a number column only if all of them are numbers, a date column only if all of them are dates, and a text column otherwise.
- If a later value in a number column is not a number, that one cell is written as text exactly as it appears in the file, rather than changed to fit the column.
- In a number column whose header contains a word such as ID, code, SKU, zip, postal, phone or ref, whole numbers are shown without a thousands separator:
100234, not100,234.
| Column | In the CSV | Excel by double-click | AnyToExcel .xlsx |
|---|---|---|---|
| SKU | 00123 | 123 (number) | 00123 (text) |
| Postcode | 01000 | 1000 (number) | 01000 (text) |
| Phone | 0612345678 | 612345678 (number) | 0612345678 (text) |
| Stock | 4 | 4 (number) | 4 (number) |
To convert a file this way, upload it to the CSV to Excel converter. The file can be up to 5 MB and no account is needed.
When does AnyToExcel still turn a code into a number?
AnyToExcel still turns a code into a number when no value in the code column starts with a zero, because nothing in the data then shows it is an identifier. A SKU column holding only 123, 456 and 789 becomes numbers. The digits are all kept, but the cells are numbers rather than text, and adding 00123 later will not match them in a lookup.
- International phone numbers with a plus sign.
+33612345678is read as the number 33612345678, which drops the+. A column of phone numbers written with spaces, such as+33 6 12 34 56 78, stays as text. - Short codes with no zero in the sample. The test covers the first 200 non-empty values of each column. If none of them starts with 0, the column is numeric; a later
00789is kept as text in its cell, but the column is mixed. - Headers do not change the type. A header called SKU or ZIP only changes how whole numbers are displayed. It does not force the column to text.