How to Keep Leading Zeros (and Long Numbers) When Opening CSV in Excel
By the DataSwitch Pro team · · 4 min read
ZIP codes, product SKUs, phone numbers, bank references and employee IDs often start with zeros or are very long. Open a CSV that contains them in Excel and you may see 2134 instead of 02134, 1.23457E+15 instead of a card-like reference, or a date where a product code used to be. Worse, if you then save the file, the original values are gone for good.
What Excel does to your values
- Leading zeros are dropped. Excel sees
00412, decides it is the number 412 and stores it as such. - Long numbers lose digits. Excel stores numbers with 15 significant digits. A 16-digit or longer value such as
1234567890123456789is displayed in scientific notation and the digits after the 15th become zeros. - Some text becomes dates. Values such as
3-4,1/2orMAR1are converted to dates. This was so common in genetics that several human genes were officially renamed (for example SEPT1 and MARCH1) to stop Excel from turning them into dates.
The root cause is the same in every case: when you double-click a CSV, Excel guesses the type of every cell, and a CSV file carries no type information to correct the guess.
Fix 1: Import the CSV and set the column to Text
- In a blank workbook, go to Data → From Text/CSV and choose the file.
- Click Transform Data to open Power Query.
- Select the affected column, then Data Type → Text. If Excel asks whether to replace the current conversion step, choose Replace current.
- Click Close & Load.
Alternatively, in the import preview, open Data Type Detection and choose Do not detect data types to import every column as text. In the older Text Import Wizard, select the column in step 3 and set its format to Text.
Fix 2: Turn off automatic data conversion (Microsoft 365)
Recent versions of Excel for Microsoft 365 include settings that control these conversions. Go to File → Options → Data → Automatic Data Conversion and untick the conversions you do not want, such as removing leading zeros, converting long numbers to scientific notation, or converting text to dates. The options apply when you open CSV files and when you paste or type data. If you do not see this section, your Excel version predates it — use Fix 1.
Fix 3: Convert the CSV to an Excel workbook first
An .xlsx file stores each cell's type, so Excel never has to guess. The CSV to Excel converter keeps values with leading zeros, numbers longer than 15 digits, hexadecimal and scientific-looking codes as text cells, while plain numbers become real numbers you can sum. Open the resulting workbook and everything appears exactly as in the original file. This is also the safest format to send to colleagues, who will otherwise run into the same problem.
Fix 4: Prepare the data for Excel
If you control the export, you have two further options:
- Export directly to
.xlsxwith text-typed columns instead of CSV. - Write affected values as formulas such as
="02134". Excel then displays the text exactly, but other software will see the formula characters, so this only suits files meant for Excel alone.
Don't save over the original
If Excel has already changed the values, do not save the CSV — saving writes the damaged values back to disk. Close it without saving and re-open it with one of the methods above. If the file was already overwritten, the lost zeros and digits cannot be recovered from it; go back to the source system and export again.
Leading zeros in code and other tools
Excel is not the only culprit. Many scripts and converters turn "00123" into the number 123 too. In Python with pandas, read affected columns as strings:
Python (pandas)
import pandas as pd
df = pd.read_csv("customers.csv", dtype={"zip": str, "customer_id": str})
# or keep every column as text:
df = pd.read_csv("customers.csv", dtype=str)DataSwitch Pro's converters apply the same rule automatically: values with leading zeros or more than 15 digits stay strings when you convert CSV to JSON, SQL or any other format, and you can untick Detect numbers & booleans to keep every value as text.
Summary
Excel removes leading zeros, truncates long numbers and invents dates because it guesses types when opening CSV files. Import with explicit Text columns, turn off automatic data conversion in Microsoft 365, or convert the CSV to an Excel workbook so the types are fixed before anyone opens it.
Try it on your own file. Free, private, and processed in your browser.
Frequently asked questions
Why does formatting the column as Text afterwards not bring the zeros back?
By the time you format the column, Excel has already stored the value as a number and discarded the zeros. The type must be set during import, not after.
Is the 15-digit limit specific to CSV files?
No. Excel stores all numbers with 15 significant digits. Long identifiers must be stored as text to stay exact, whatever the source.
Does Google Sheets keep leading zeros?
When importing, Google Sheets offers a Convert text to numbers, dates, and formulas option. Untick it to keep values exactly as written.