Why does Excel drop leading zeros from my CSV?
Quick answer
Excel treats every CSV value as "General" format when it opens the file, which means anything that looks like a number is read as one - and a real number can never start with a zero, so it is dropped. Converting the CSV to Excel with those columns explicitly typed as text, rather than opening the CSV directly, avoids the problem entirely.
- 1Convert the CSV to a real .xlsx first, with the affected column set to Text.
- 2Or, in Excel's own Import wizard (not just "Open"), set that column's format to Text before finishing the import.
- 3Or prefix each value with an apostrophe ('02134) if you are editing by hand - this forces text without showing the apostrophe itself.
Free, no sign-up, and your file is read on your own device rather than uploaded.
A CSV file has no concept of a "text" or "number" column - every value is just characters, and it is up to whatever opens the file to decide what each one means. Excel's default behaviour when you double-click a .csv is to guess, column by column, and its guess for "02134" is "this is the number two thousand, one hundred and thirty-four" - which is wrong for a ZIP code, but indistinguishable from a real number using Excel's own logic.
The fix has to happen before Excel makes that guess, either by using Excel's more deliberate import path (which lets you set column types) or by converting the file to .xlsx first with the right columns already marked as text.
Step by step
Identify which columns need to stay text
ZIP codes, account numbers, IDs, phone numbers - anything where a leading zero is part of the actual value, not padding.
Convert with those columns protected
Columns with a leading zero are detected and protected automatically.
CSV to ExcelOpen the result normally
The .xlsx file already has the correct column types, so there is no import dialog left to get wrong.
Tips
- This is not unique to ZIP codes - any identifier with a leading zero (some invoice numbers, some employee IDs, IBAN-style codes in some countries) has the exact same problem in Excel.
- If you only need to fix one or two cells and are already in Excel, prefixing the value with an apostrophe ('0501) forces it to be treated as text without the apostrophe itself showing.
Common problems
The leading zero is already gone - the file was saved after opening it in Excel.
Unfortunately, once Excel has interpreted the value as a number and you have saved the file, the original text is gone - it has to be re-created from the original source, not recovered from the Excel file.
Frequently asked questions
- Why does this only happen with some columns and not others?
- Excel decides column by column based on what the values look like - a column of names is obviously text, but a column of digit strings looks exactly like a column of numbers unless something (like a leading zero) tips it off.
- Does converting to .xlsx first really fix it for good?
- Yes - once a cell is explicitly typed as text in the .xlsx file itself, Excel has nothing left to guess. The ambiguity only exists at the moment a plain CSV is opened.
Related data tools
Related articles
- Excel & Data
Why does Excel show my CSV numbers as 1.23E+12?
Excel's number type reliably stores about 15 significant digits. A 16-digit card or account number read in as a number gets rounded to fit and displayed in scientific notation - and the digits past the 15th are genuinely gone, not just hidden behind the display format. The fix is to keep that column as text from the start, rather than trying to reformat it back afterward.
- Excel & Data
Why does my CSV open in one column in Excel?
This happens when the file uses a different delimiter (usually a semicolon) than the comma Excel expects by default in your regional settings - common with files exported from systems set up for a European locale, where a comma is the decimal separator and a semicolon separates fields instead. Converting the file with the correct delimiter detected, or using Excel's Text to Columns feature, fixes it.
Ready to do it?
Free, no sign-up, and nothing is uploaded to a server.