Why Excel Drops the Leading Zeros From Your CSV Codes, and How to Keep Them (2026 Guide)

Diagnose it in 30 seconds: the file is fine, Excel guessed wrong

The first thing to establish when your codes lose their zeros is whether the data is damaged or only misread, because the two cases have very different fixes, and the wrong fix can turn a display problem into real data loss. Here is the 30-second test. Open the same file in a plain text editor, Notepad on Windows or TextEdit in plain-text mode on a Mac. If the codes show their full zeros there, 00123, 02138, 07477, your data is intact and only Excel display is wrong. The file on disk holds the right characters, and Excel changed their meaning when it opened the file. That is the common case, and every fix in this guide starts from it.

The second clue is which values changed. If ordinary words, short numbers, and punctuation look fine, but codes with leading zeros lost them, long IDs lost their last digits, and big numbers show in scientific notation, you are looking at type guessing, not a delimiter problem and not corruption. A delimiter problem puts whole rows in one column. Corruption scrambles everything or drops rows. Type guessing is surgical: it changes exactly the values that look like numbers, and leaves everything else untouched. If 00123 became 123, 02138 became 2138, and a 16 digit ID ends in a zero it did not have before, Excel treated text as numbers, and that tells you both the cause and the fix.

The third check decides whether rescue is still possible. If the text editor also shows the stripped values, the file itself has already been re-saved after the conversion, and the zeros may now be gone from that copy. Do not panic yet, and above all do not save over the original again. The rescue section later in this guide covers exactly this case, including the one situation where the digits are truly unrecoverable: when a long ID was truncated to 15 digits, the lost digits are gone from that copy, and only the source data can regenerate them. Until you have checked, keep the original file untouched, work on a copy, and never save a converted file back over the good one.

Why Excel guesses: a CSV file carries no types

To understand why this keeps happening, you need one fact about the CSV format: a CSV file is plain text with no header that says which values are numbers, which are text, and which are dates. A spreadsheet file carries its cell types with it. A CSV file carries neither types nor formatting. It is just characters, commas, and line breaks. When a program opens it, the program must decide what each value means, and different programs decide differently. Google Sheets and Python keep CSV values as text unless told otherwise, which is why the same file looks correct in them. Excel, when you double-click a CSV file, guesses: anything that looks like a number becomes a number, anything that looks like a date becomes a date, and it does not check whether that guess was right.

That guess is the whole problem. A number, by definition, has no leading zeros: the number 123 and the code 00123 are the same number, but they are not the same code. A ZIP code, a SKU, an employee ID, or a phone prefix is not a quantity. It is a label made of digits, and its leading zeros are part of the label, exactly like the letters in a word. Excel does not know that. It sees digits, applies its number rule, and drops the zeros, because for a real number the zeros carry no information. The same logic explains the second casualty: Excel numbers keep only 15 significant digits, so a 16 digit or longer ID gets its extra digits replaced with zeros. The file did not change. The reader changed the meaning of the characters.

It also explains why this problem appears so often with files converted from JSON. JSON does distinguish types: the string "00123" and the number 123 are different values, and a JSON to CSV conversion writes the string out as the characters 00123. The conversion is correct. The opening is mismatched. The converter on this page, for example, was run on a sample holding the codes 00123 and 00456, the ZIP codes 02138 and 07477, and an 18 digit order ID, and it produced every value byte-identically in the CSV, with no quoting tricks and no digits dropped. Knowing that the mismatch sits at the opening step, not the conversion step, tells you where to fix it: at the import, before Excel guesses, as the next sections show.

Three ways the guess destroys data, and how to tell them apart

Type guessing destroys data in three distinct ways, and telling them apart matters because only some of them are repairable. The first is leading zeros dropped: 00123 becomes 123, 02138 becomes 2138. The digits that remain are correct, but the code is now wrong: a ZIP code that lost its zero points at a different place, a SKU that lost its zeros will not match the warehouse system, and a barcode scanner may reject the shortened code. The second is precision truncation: any ID longer than 15 digits loses its tail. A 16 digit account ID like 1234567890123456 becomes 1234567890123450, displayed as 1.23457E+15. The last digits are not hidden, they are replaced with zeros, and the ID now points at a different record or at no record at all.

The third way is subtler, and it catches people who think they fixed the problem. Scientific notation display looks like data loss but is not, yet. A value displayed as 1.23E+15 may still hold its full digits underneath if it is within the 15 digit limit, and widening the column or changing the format shows them again. But if the value exceeds 15 digits, the display is honest: the digits really are gone. The test is the formula bar. Click the cell and read the formula bar, not the cell display. If the formula bar shows the full value, only the display needs fixing. If the formula bar shows the truncated value, the data itself changed, and only the rescue section can help. Never trust the cell display alone for codes and IDs.

The code block below shows the exact outputs, produced by running the real converter on this page on a three row sample. The CSV output is byte-identical to the JSON input for every code, which proves the converter is not the culprit. The right column shows what Excel makes of those same values on double-click, which is the behavior this guide fixes. Notice the pattern: the converter preserves, Excel guesses, and the guess is where every digit is lost.

CSV file (from this page converter, verified):\nsku,zip,name,orderId\n00123,02138,Widget A,\n00456,07477,Widget B,\n00078,,Widget C,123456789012345678\n\nWhat Excel shows on double-click:\nsku -> 123, 456, 78 (leading zeros dropped)\nzip -> 2138, 7477 (leading zeros dropped)\norderId -> 1.23457E+17, stored as 123456789012345000 (last 3 digits replaced with zeros)

Notice what the table proves: nothing is missing from the file, and everything is missing from the Excel view. That is why the import fix works so completely. When you tell Excel a column is Text before loading, it stores the same characters with no conversion at all, and every code snaps back to itself, with no editing, no find-and-replace, and no data entry. If your file matches this pattern, skip the myth-busting section and go straight to the import fix. You do not have a damaged file. You have a misread one.

Four fixes that do not work, and why people keep trying them

Before the fixes that work, it is worth clearing away the four fixes that do not, because each one wastes time and one of them makes things worse. The first is quoting the values in the CSV. Many guides claim that writing "00123" with quotes protects the zeros. It does not, in Excel. Excel strips the quotes during import and then applies the same number guess to the value inside, so "00123" becomes 123 exactly like bare 00123. Quoting controls how commas inside a value are handled, not how Excel types the value. If someone sold you a converter that quotes codes to protect them, the quotes protect the commas, not the zeros.

The second is formatting the column as Text after opening the file. This changes how future entries are treated, but it does not restore digits that were already dropped when Excel read the file. The zeros were discarded at read time, and formatting cannot invent characters that are no longer there. The third is the custom format 00000, which pads the display to five digits. It works as display only, for fixed-length codes, and only when the digits survived: 123 displays as 00123, but the stored value is still 123, so any export writes 123, and any lookup against the real code fails. Worse, it fails silently on variable-length codes, because a six digit code padded to five digits is unchanged, and a four digit code padded to five gains a zero it never had.

The fourth is the apostrophe trick, typing a leading apostrophe before the code, as in a cell typed as 00123 with an apostrophe in front. That works for values you type by hand, because the apostrophe tells Excel to store the entry as text. It does not work for a CSV import, because the apostrophe becomes part of the file content: a CSV cell holding an apostrophe followed by digits imports as a text value that literally starts with an apostrophe, which breaks lookups and exports. Keep the apostrophe for hand-typed entries, and use the import fix for files. Knowing which fixes fail saves you from the most common trap of all: believing the problem is solved because the display looks right, while the stored values are still wrong.

Fix 1: import the file and set code columns to Text

This is the fix to try first, because it changes nothing about your system, nothing about the file, and it works on any CSV whatever its delimiter or encoding. Instead of double-clicking the file, open Excel first, then import the file through the guided import, where you choose the type of each column yourself. The preview in the import dialog shows you immediately whether you picked right: the codes display with their zeros in the preview exactly as they will on the sheet. If the preview still shows stripped codes, you have not changed anything yet, and you can adjust the column types before loading.

• ▸Open a blank workbook in Excel. Do not double-click the CSV file.
• ▸Go to Data, then From Text/CSV, and select your file.
• ▸In the preview dialog, check the delimiter and the file origin, then click Transform Data instead of Load, to open the Power Query editor.
• ▸In the Power Query editor, find the automatic Changed Type step and remove it, because that step is where the number guess happens.
• ▸Select every code column, ZIP codes, SKUs, IDs, phone prefixes, and set the data type to Text.
• ▸Click Close and Load. The codes arrive as text, with every leading zero and every digit intact.

Make this your default way of opening CSV files, not just your rescue move. Double-clicking is a guess about the delimiter, the encoding, and the type of every column. Importing is a choice about all three, and the choice takes about a minute longer. On a Mac, the same route works through Data, then From Text, with the column types set in the import step. Tools that read CSV as plain text, like Python csv module, never guess at all, which is why a file that looks broken in Excel reads correctly in a script. That difference is itself a diagnosis: if a script shows the zeros and Excel does not, the file is fine and Excel guessed wrong.

Fix 2: turn off Excel automatic data conversion (Microsoft 365 and Excel 2024)

If you open CSV files every day, importing each time gets old, and the durable fix is to change what Excel guesses by default. Recent Excel versions, Microsoft 365, Excel 2024, and their Mac equivalents, carry a setting called automatic data conversion, under File, then Options, then Data. It lists the conversions Excel applies on its own, including removing leading zeros from numeric text, truncating numbers to 15 digits, and converting letter-and-number strings that look like dates or scientific notation. Uncheck the conversions you do not want, and Excel will leave those values as text on future opens, including double-click opens.

Two cautions before you rely on this setting. First, it changes future behavior only. It does not repair cells that were already converted, and it does not restore digits that were already dropped. A file you opened last week still holds the converted values in the workbook, and only a fresh import from the original file will bring the codes back. Second, the setting lives on your machine, not in the file. A colleague who opens the same file on a machine with the default setting will still see stripped codes, and a file you send out carries no protection with it. Treat the setting as a convenience for your own machine, and treat the import fix as the reliable method for files you share. The companion guides on the one-column problem and the encoding problem cover the other two guesses Excel makes on open, delimiter and encoding, and they are linked in the related guides below.

Fix 3: for files you produce, keep codes as text at the source

If you produce the CSV, the strongest fix is to make the source data unambiguous before it ever reaches Excel. In JSON, that means keeping codes as strings, not numbers: the string "00123" converts to the characters 00123, while the number 123 converts to 123, with no zeros to preserve. This is a source-data decision, not a converter setting, because no converter can invent zeros that the source number never had. If your source system already stores a ZIP code as a number, the zeros are gone before conversion, and only the source system can restore them, for example by re-exporting the code as a fixed-width string.

For values that already lost their zeros but have a fixed length, there is one legitimate reconstruction: the TEXT function. If every code in a column is five digits, =TEXT(A2,"00000") rebuilds the display and the text value from the stripped number, because the missing digits are all zeros and their count is known. Copy the results and paste them as values to lock them in. But this works only when the length is fixed and the only damage is dropped leading zeros. It cannot reconstruct a truncated long ID, because the lost digits were not zeros, they were unknown digits replaced by zeros, and no formula can guess them. If your IDs vary in length or exceed 15 digits, do not pad, regenerate from the source.

Rescue a file that was already saved with stripped codes

So far every fix has assumed the file on disk is still correct and only the Excel view is wrong. But sometimes the stripped version has already been saved, and the question becomes whether the original codes can still be recovered from the saved copy. The answer depends on exactly what the save did, and there are two cases to tell apart. In the first case, the file on disk still holds the original characters, and only the workbook display is wrong. That is the case the text-editor test detects: the editor shows the zeros, so nothing is lost, and Fix 1 repairs the view. In the second case, the file on disk now contains the stripped values themselves, because someone opened it in Excel, saw the shortened codes, and saved it back to CSV, writing the converted values into the file.

The second case is recoverable only in narrow circumstances. If every code has a fixed length and the only damage is dropped leading zeros, the TEXT function from Fix 3 rebuilds them, because the missing characters are known to be zeros. Work on a copy, never on the only version, and check the result in a text editor before opening it in Excel again. If the damage is precision truncation, an ID longer than 15 digits with its tail replaced by zeros, the original digits are gone from that copy. No decoding, formatting, or formula can recover digits that were replaced by placeholders, because the placeholders carry no information about which digits they replaced. In that case, stop repairing the copy and regenerate the file from its source: re-export it from the database, the tool, or the JSON it came from, and this time open it with Fix 1. And whatever you do, keep the damaged copy aside until the regenerated file checks out. Deleting the only copy before verifying the replacement is how a recoverable problem becomes a lost dataset.

What this means for files from this page converter

The converter on this page produces exactly the kind of file this guide is about, so it is worth stating its behavior plainly. The download preserves codes exactly as the JSON holds them. That was verified against the real code, not assumed: the conversion engine was run on a three row sample holding the codes 00123, 00456, and 00078, the ZIP codes 02138 and 07477, and the 18 digit order ID 123456789012345678, and it produced every value byte-identically in the CSV, with the codes unquoted because they contain no delimiter, and the long ID untouched. So if your converted file shows stripped codes in Excel, the conversion did not damage anything. The file is correct, and Excel guessed the types on double-click.

That gives you two clean options for converted files. The first is the import route: open the downloaded file with Data, then From Text/CSV, click Transform Data, set every code column to Text, and load. The codes will read correctly, and you have changed nothing about the file, so it stays valid for every other tool. The second is the automatic-conversion setting from Fix 2, for your own machine. Do not re-convert the data to fix a type-guessing display problem, and do not paste the converted output through a spreadsheet and save it back, because that round trip can introduce exactly the delimiter, encoding, and type mismatches this guide and its companions exist to prevent. Fix the opening, not the data.

One more converter detail matters here. The converter lets you choose the delimiter before downloading, and that choice is independent of type guessing. A file can have the right delimiter and stripped codes at the same time, or the right codes and the wrong delimiter, or both wrong at once. If your converted file opens in one column and the codes look wrong, fix both settings in the same import: pick the delimiter you chose at conversion time, and set the code columns to Text. The companion guide on the one-column problem covers the delimiter half in detail, and the encoding guide covers the character half, and both are linked in the related guides below.

Prevention checklist: export codes so nobody has to fix them

Type guessing is a mismatch between writer and reader, so prevention means making the writer declare what the values are, in a way the reader will honor. Run through this checklist when you produce a CSV, and the next person who opens it will not need this guide. First, keep codes as strings at the source. If your data holds ZIP codes, SKUs, or IDs, store them as text in the source system, because a number type drops the zeros before any export happens. Second, if the audience is Excel, tell them how to open the file: a one-line note saying import with code columns set to Text prevents the whole problem, and it costs nothing to include with the file.

Third, never save a rescued file back over the original until you have verified the rescue in a text editor. A save from Excel rewrites types, delimiters, and encoding with Excel own defaults, which can reintroduce the mismatch for the next reader, or turn a display problem into real data loss. Fourth, when you receive a file, open it in a text editor first if anything looks off, and confirm the codes before you let any spreadsheet save over it. And fifth, keep the source data. A CSV is a flattened snapshot, not the data itself, so if a rescue goes wrong, the source lets you export again cleanly instead of repairing a damaged copy. Most code horror stories end with someone discovering too late that the damaged CSV was the only copy. Do not let that be your file.

Related guides

• Why Your CSV Opens in One Column in Excel, and How to Fix It (2026 Guide) →
• Why Your CSV Shows Weird Characters in Excel, and How to Fix the Encoding (2026 Guide) →
• How to Convert JSON to CSV: 3 Reliable Methods (2026 Guide) →
• Why Excel Changes Your CSV Dates, and How to Keep Them (2026 Guide) →
• Why Saving Your CSV in Excel Changes It, and How to Keep the Original Safe (2026 Guide) →
• Why Excel Won't Open Your Big CSV File, and What to Do Instead (2026 Guide) →

Frequently asked questions