Guide
Stop Excel Removing Leading Zeros from CSV Files
Published
You export a customer list, open it in Excel, and ZIP code 02134 has become 2134. Employee number 000581 is now 581. A 16-digit card reference reads 4.53211E+15. The CSV file itself was fine. Excel changed the values while opening it.
Why Excel Does This
A CSV file is plain text with no information about what kind of data each column holds. When you open one, Excel guesses: anything that looks like a number becomes a number, and numbers do not have leading zeros. Three related conversions cause most of the damage:
| In the CSV | After opening in Excel | Why |
|---|---|---|
00742 | 742 | Converted to a number |
4532118899001234 | 4.53212E+15, saved as 4532118899001230 | Excel keeps only 15 significant digits |
MAR1 or 1-2 | 1-Mar, 2-Jan | Converted to a date |
Only the first row is this guide’s subject, and it is the mildest of the three: a leading zero can be put back if you know how long the code should be. The second cannot. Microsoft’s documented precision limit is 15 digits, so in a 16-digit number “any numbers past the 15th digit are rounded down to zero”, and no amount of reformatting brings them back — stop Excel turning long numbers into 4.5E+15 covers that case on its own.
Fix 1: Turn Off Automatic Data Conversion
Newer Excel can switch these conversions off for good, including for files you open by double-click. Go to File › Options › Data and untick Remove leading zeros and convert to a number under Automatic Data Conversion. Microsoft lists the setting for Excel for Microsoft 365 and Excel 2024 on Windows and Mac; if that section is not in your Options, your version does not have it. How to convert a CSV to Excel without breaking your data goes through all four switches and what each one protects.
Fix 2: Import the CSV and Mark Columns as Text
This works in every modern Excel version, and it is the route Microsoft itself recommends for long identifiers:
- Open a blank workbook — not the CSV — and go to Data › From Text/CSV.
- Choose the file, then open Power Query (the button is Transform Data, or Edit in older builds).
- Select the ZIP code, ID or phone column and set Home › Transform › Data Type › Text. When asked, choose Replace current.
- Click Close & Load. The values keep every digit.
One thing decides whether this works: the type must be set at the step where Excel first detected types. If Power Query has already added a Changed Type step, delete it from the Applied Steps pane rather than typing over its result. If the preview already reads 742, you are too late.
Fix 3: Don’t Round-Trip the CSV through Excel at All
Most damage happens when someone opens a CSV in Excel only to do a quick job — split it up, remove duplicates, merge a few files — and saves it again. Saving writes the converted values back into the CSV, and the next system that imports the file gets 742 instead of 00742.
If the job is mechanical, use a tool that treats the file as text. The split, merge and remove duplicates tools copy each row exactly as it appears in the file, so leading zeros, long numbers and date-like codes pass through untouched. They write CSV text only: no .xlsx files, no cell editing, no sorting.
Fix 4: Ask for Quoted Values in the Export — With a Caveat
Some systems can wrap values in quotes, like "00742". That is the correct way to write a CSV, but Excel still removes the zeros when you double-click the file, because quotes in CSV only mean “this value may contain commas”, not “this is text”. Quoting protects your data from other programs, not from Excel’s automatic conversion.
The trick you may see online, writing values as ="00742", does keep the zeros in Excel, but it puts an Excel formula into the file, which breaks the value for every other program that reads it. Use it only for files meant purely for people to look at in Excel.
If the Zeros Are Already Gone
If a code column always has the same length, such as 5-digit US ZIP codes, you can restore it in Excel with a custom number format of 00000, or with the formula =TEXT(A2,"00000"). For values of varying length, or long numbers that were rounded, the information is lost; go back to the original export.
Two neighbouring problems are worth knowing apart from this one. If the file arrives with every row crammed into column A, nothing has been converted yet and the delimiter is the culprit: why your CSV opens in one column in Excel. And if the file is too long for a worksheet in the first place, see how to open a CSV that is too large for Excel.