Why Excel Drops Leading Zeros When You Open a CSV File

You open a CSV by double-clicking it. The zip code 02134 shows as 2134. The product code 00123 shows as 123. The account number 1234567890123456 ends in a zero it never had. Nothing has been changed in the file, and yet the data in front of you is wrong. This is not a bug in the file or a random fault. It is the direct result of what a CSV is, and what Excel does about it. Knowing the mechanism tells you which fixes are real, and which ones only hide the problem until the next save.

What is actually in the file

Take a three-line file:

code,zip,amount
00123,02134,10.50
00045,90210,7.00

The file contains characters. The first field of the second line is the five characters 0, 0, 1, 2, 3. A CSV has no way to say "this column is text" or "this column is a number". Every field is just a string of characters between delimiters, as RFC 4180 describes. Types belong to the program that reads the file.

What Excel does when it reads it

When you double-click a CSV, Excel opens it directly and decides a type for each cell by looking at the text. The rule is roughly: if it can be read as a number, a date, a time, a percentage or a boolean, it becomes one. 00123 can be read as a number, so it becomes the number 123. 02134 becomes 2134. 10.50 becomes 10.5, and the cell format may or may not show the trailing zero. Values such as 1-2 or 3/4 may become dates. 1E3 becomes 1000.

The number 123 has no leading zeros, because a number is a value and not a string of digits. Excel is not removing zeros from a text; it has replaced the text with a different thing. After the conversion the information that there were two leading zeros does not exist in that cell, and no cell format can bring it back truthfully. A custom format such as 00000 can display 123 as 00123, which is a display trick that works only for a fixed width and leaves the underlying number as 123.

The decision is made for each column of the first rows, silently, and with no warning. That is the usability trap: the file looks fine until someone notices the zip codes.

When the damage becomes permanent

The CSV on disk is unchanged as long as you do not save it. Close it without saving and 00123 is still there. The trap is the save. When you press save, Excel writes what is in the cells, and the cell now holds 123, so the file now contains 123. Excel also warns you that saving as CSV will lose features, and many people click "Keep format" without seeing that the loss includes the zeros. From then on the zeros exist nowhere. A file that passes through an Excel session and back is a file that has been edited, whatever you intended.

Two more losses come with the same mechanism. Excel keeps 15 significant digits of a number. A 19-digit identifier such as 1234567890123456789 becomes the number 1.23456789012346E+18 and is written back as 1234567890123460000, with the last digits replaced by zeros. And values that look like dates are converted and written back in the locale's date format, so a gene symbol SEPT2 has famously become the date September 2.

The fix that works: import as text

Do not open the file by double-click. Import it, and tell Excel which columns are text.

In current Excel: Data > From Text/CSV, choose the file, then Transform Data to open Power Query. Select the column, set its type to Text, and close and load. Power Query keeps the type with the query, so refreshing from an updated file keeps the zeros. In the first dialog, Excel's preview can detect types from the first 200 rows; you can set "Do not detect data types" in the dialog to stop the guessing and get text for everything.

The older route, still available in some versions, is the Text Import Wizard: in step 3 select each column and set its format to Text. The setting means the cell holds exactly the characters in the file.

For one cell at a time, typing an apostrophe first ('00123) stores text, and formatting the cell as Text before pasting does the same for a column. Both are fine for manual work and neither survives a file opened by double-click.

The fix for the producer

If you produce the CSV, Excel users will open it by double-click, so you decide what they see. Three choices exist, none perfect.

Ship the identifiers as they are and document that the columns must be imported as text. This is the correct file and requires the reader to do the right thing.

Write an Excel-only wrapper in the values: ="00123". Excel reads it as a formula that yields the text, and the zeros survive. Every other reader, including scripts, sees the literal characters ="00123" in the data. Use it only for a file whose sole audience is Excel.

Give them a real workbook instead. Write an .xlsx with a text-typed column, which carries the type inside the file, and keep the CSV for machines (converting CSV to a workbook with formatting). A column formatted as text in a workbook is the only fully reliable way to give an Excel user their zeros.

Which values should be text

A value should be text if you would never add it, average it or compare it by size. Zip and postal codes, phone numbers, national and account identifiers, product and SKU codes, barcodes, ISBNs and order numbers all qualify. A phone number of 0612345678 is not a number. Dates in a form you want preserved as written (01/02/2024, which is ambiguous between January and February) deserve the same treatment until someone decides the format. Treat everything as text by default, and convert the columns you know are quantities.

The same rule applies outside Excel. Pandas will turn 00123 into 123 with read_csv unless you pass dtype=str or a dtype for the column, and DuckDB's sniffer may do the same unless you give it all_varchar=true or types. Reading everything as text first is the habit that prevents the whole class of problem.

How to tell it worked

After the import, click a cell: the formula bar shows 00123, and the cell is left-aligned, which is Excel's default alignment for text. Use =ISTEXT(A2), which returns TRUE. Compare the length: =LEN(A2) is 5 for 00123 and 3 for the damaged 123. If you saved a file, open it in a plain text editor or run head on it and confirm the zeros are in the bytes. Check a long identifier by its last digits against the source.

When the zeros were never in the file

Sometimes the zeros were already gone before the CSV was written: a database column typed as integer, or an earlier trip through Excel. The file then really contains 123, and no import setting brings them back. If the width is fixed (five-digit codes, five-digit zip codes), you can restore them with padding in the tool you use afterwards (lpad(code, 5, '0') in SQL, str.zfill(5) in pandas), but only when you know every value has the same width; a zip code of 2134 might be 02134 or the first digits of a longer code. The reliable fix is upstream, at the first place the value became a number. For the broader picture of opening CSV files without damage, see the guide to CSV in Excel and Sheets, and for encoding problems that appear on the same double-click, see opening a UTF-8 CSV in Excel.

Further reading: RFC 4180; Microsoft: import or export text files.