Excel removes leading zeros from CSV files because it converts anything that looks like a number into a number, so 00123 becomes 123, and it does the same damage to long IDs (rounded after 15 digits) and to codes that look like dates. If you then save the CSV, the change is permanent. To keep your data, import the columns as text with Power Query, turn off the conversions in newer Excel, or clean the file in a tool that never converts types. CSVPrune treats every value as text, so leading zeros and long IDs are kept, and you can clean, dedupe, and export without opening the file in Excel at all.
This is one of the most common ways customer lists, product catalogs, and payroll files get quietly corrupted.
Open and clean CSVs with every leading zero and long ID left exactly as it was.
Get it from Microsoft
Why does Excel remove leading zeros?
Microsoft explains it in Keeping leading zeros and large numbers: “Excel automatically removes leading zeros, and converts large numbers to scientific notation, like 1.23E+15, in order to allow formulas and math operations to work on them.” The article lists the kinds of data this hits: “social security numbers, phone numbers, credit card numbers, product codes, account numbers, or postal codes.”
A CSV has no column types. Every value is just text between commas. When you double-click a CSV, Excel guesses a type for each value, and a ZIP code like 02134 looks like the number 2134.
What else does Excel change when it opens a CSV?
Leading zeros are the best-known problem, not the only one. The same Microsoft article lists four automatic conversions:
| Conversion | Example in the CSV | What Excel shows | Can you get it back? |
|---|---|---|---|
| Removing leading zeros | 00123 | 123 | Only from the original file |
| Truncating to 15 digits, scientific notation | 4111111111111111 | 4.11111E+15, last digit becomes 0 | Only from the original file |
| Numbers around the letter E | A part code such as 12E4 | A number in scientific notation | Only from the original file |
| A continuous string of letters and numbers as a date | A code that happens to look like a date | A date | Only from the original file |
The second row deserves extra attention. Microsoft says “Excel has a maximum precision of 15 significant digits, which means that for any number containing 16 or more digits, such as a credit card number, any numbers past the 15th digit are rounded down to zero.” Order numbers, tracking numbers, and database IDs are often 16 digits or more.
Why can’t I just reformat the column afterward?
Because the zeros are already gone. Microsoft’s article notes that a custom number format “will not restore leading zeros that were removed prior to formatting,” and that formatting a column as Text “will not change numbers that have already been entered.”
Worse, if you press Save while the CSV is open, Excel writes the converted values back into the CSV. The next system to import that file gets 123 instead of 00123. Always keep the original export until you have checked the result.
How do I open a CSV in Excel without losing leading zeros?
Microsoft documents four approaches. Use the first one that fits your version and workflow.
1. Turn off automatic data conversions (newer Excel)
Microsoft says the Automatic Data Conversions setting is available in Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, and Excel 2024 for Mac. It lets you turn off each of the four conversions above. This is the best fix if you have a supported version, because it applies every time.
2. Import with Power Query and set columns to Text
This works in more versions and gives you column-by-column control:
- Open a blank workbook and go to Data, From Text/CSV.
- Choose the file. Excel shows a preview.
- Select Edit (or Transform Data) to open the Query Editor.
- Select the ID columns, using Ctrl+click for several.
- Go to Home, Transform, Data Type, Text, and choose Replace Current.
- Select Close and Load.
Microsoft notes that Data, Refresh then reapplies the same steps when the file changes.
3. Use a custom number format (for display only)
For codes under 16 digits that only need to look right in this workbook, a custom format such as 00000 shows five-digit ZIP codes with their zeros. It does not change the underlying value, so it is the wrong fix for a file you will export.
4. Type an apostrophe
For a single cell, typing an apostrophe before the number makes Excel treat it as text. This does not scale to a file.
Fix encoding, delimiters, and duplicates without Excel converting a single value.
Get it from Microsoft
How does CSVPrune avoid the problem?
CSVPrune is a Windows CSV cleaner that never guesses types on load. Its product page says that “because values are treated as text, leading zeros and long IDs are preserved instead of being mangled into scientific notation.” That one design choice removes the whole category of problems above.
Then you can do the cleaning you opened Excel for in the first place:
- Open large files: search, filter, and sort 100k+ rows in a fast grid, which the product page describes as working “without Excel’s freezes or row limit,” with a file ceiling around 500 MB.
- Fix encodings and delimiters: UTF-8, UTF-16, Latin-1, and comma, semicolon, tab, or pipe.
- Trim, normalize, and find and replace, including regex, scoped to any column.
- Dedupe by any key column, plus fuzzy matching.
- Profile columns and filter to only invalid rows, with a data-quality score.
- Toggle original versus cleaned to check every change before export.
- Export to CSV, JSON, or XLSX, or run an audited export with a before and after sidecar file.
- Save the steps as a recipe and replay them on next week’s export, a whole folder, or a watch folder.
It runs with no network connection, so customer data stays on your PC.
What is a safe workflow for CSVs with IDs?
- Keep the original export untouched in its own folder.
- Do not double-click the CSV to open it in Excel.
- Clean in CSVPrune (or import to Excel with columns set to Text).
- Check the ID columns in the original versus cleaned view.
- Export a new file with a new name.
- Spot-check a few rows in the receiving system after import.
If you only need to view the file, CSVPrune’s grid is enough. If someone needs it in Excel, export to XLSX from CSVPrune, then check the ID columns once it is open in Excel.
Frequently asked questions
Why does my ZIP code lose its first zero in Excel?
Excel reads 02134 as the number 2134. Import the column as text, turn off automatic conversions in newer Excel, or open the file in a tool that keeps text as text.
Why do my 16-digit numbers end in 0 in Excel?
Excel keeps only 15 significant digits. Digits after the 15th become zero. Use a text column for long IDs.
Can I recover leading zeros after saving the CSV in Excel?
Not from that file. Go back to the original export.
Does CSVPrune change my data when it opens a file?
No. It treats values as text and only changes what you tell it to, with an original versus cleaned toggle to check.
Try CSVPrune
Get CSVPrune from the Microsoft Store and read the CSVPrune product page. Related: open a large CSV file on Windows and fix CSV encoding and delimiter problems.
Clean the CSV, keep the zeros, export with confidence.
Get it from Microsoft
Microsoft, Windows, Excel, and Power Query are trademarks of the Microsoft group of companies. Other product names are trademarks of their owners. CSVPrune is not affiliated with or endorsed by any of them.
Last updated 17 October 2026. Published by Automata Labs, a software company in Canada.