Google Sheets Deleted the Leading Zeros From My CSV
The quick answer: Google Sheets strips leading zeros because, by default, it converts anything that looks like a number into a number — and 00501 as a number is just 501. To stop it, import with File → Import and untick “Convert text to numbers, dates and formulas”. That one checkbox keeps every value as text, so ZIP codes, SKUs and barcodes arrive exactly as written. The rest of this guide covers the fix in detail, the numbers that turn into 1.2E+11, and honestly which lost data you can and can’t get back.
Why Google Sheets does it
A CSV file is plain text. It has no way to mark a column as “these are codes, not quantities,” so when Sheets reads the file it guesses the type of every cell. A run of digits looks like a number, and a number has no use for a leading zero, so 00501 is stored as 501, 0071589 becomes 71589, and a New Jersey ZIP like 07030 loses its zero and fails the next validation step that expects five characters. The zero was never wrong in your file — it was removed the moment Sheets decided the cell was a number.
The same guess causes two neighbouring problems in one import: long identifiers collapse into scientific notation (covered below), and values such as 1-2 or MAR1 get read as dates. All three come from the identical root cause, which is why the single fix below solves all of them at once.
The one setting that prevents it
Import through the menu, not by dropping the file into Drive, and turn conversion off. Opening a CSV straight from Google Drive runs the same automatic conversion; the import dialog is the only place you get a choice.
- In a Google Sheet, open File → Import.
- Choose Upload and select your CSV file.
- In the import dialog, find “Convert text to numbers, dates and formulas” and untick it.
- Click Import data. Every column now arrives as text —
00501stays00501.
If instead you are pasting rows into a sheet that already exists, select the destination columns first and set Format → Number → Plain text, then paste. Plain-text formatting has to be in place before the data lands, because it controls how incoming text is interpreted, not how an existing number is shown.
Getting the zeros back after they’re already gone
Short answer: if the code was only shortened, yes; if it was truncated, no. The distinction decides everything:
| What happened | Example | Recoverable? |
|---|---|---|
| Zeros dropped from a fixed-width code | 07030 → 7030 | Yes — re-pad to the known width |
| Barcode shown in notation | 8.85909E+11 | Format only — see below |
| 16+ digit number rounded to 15 significant digits | last digits became 0 | No — re-export from source |
To rebuild a fixed-width code inside the sheet, use the TEXT function against the known length — for US ZIP codes that is five: =TEXT(A2,"00000"), or =TEXT(A2,"0000000000000") for a 13-digit EAN. That works only when you know every value should be the same width and the shortening didn’t also collapse the number into notation.
Repair the CSV before it ever reaches Sheets
Cleaner than fighting the import: fix the raw file first, then import it with conversion off. Our free browser tool re-pads leading zeros to the width you choose and expands any scientific notation back to full digits — all locally, nothing uploaded.
Open the leading-zeros fixer →Long numbers and scientific notation
The same import that eats your zeros also turns a 12-digit barcode into 8.85909E+11. Sheets stores numbers as double-precision floats, which hold at most about 15 significant digits — identical to Excel — so a 16-digit account number loses its tail on the way in. Two honest outcomes: a value merely displayed in notation is fine (switch the column to Format → Number or re-import as text and the digits return), but a value that was truncated past 15 digits is permanently short and has to come from the original export. Expanding the notation in the CSV first — with our scientific-notation tool — restores every digit that is still in the file and makes the format valid again for the next system that reads it.
Google Sheets vs Excel: which is safer?
Google Sheets is the gentler of the two, because it asks. The import dialog’s conversion checkbox is a genuine opt-out that Excel’s double-click path never offers — open a CSV in Excel by double-clicking and the damage is already done before you can object. If your only goal is to look at a CSV without harming it, importing into Sheets with conversion off is one of the safest ways to do it. The equivalent careful path in Excel is Data → From Text/CSV with each identifier column set to Text, which we cover in the Excel scientific-notation guide and in why leading zeros disappear from CSV files.
One caveat that surprises people: a value stored as text will sit left-aligned with no green triangle in Sheets, but the same file opened in Excel may show Excel’s “number stored as text” warning. That warning is harmless here — it is exactly what you want for a code column.
Frequently asked questions
How do I stop Google Sheets from removing leading zeros?
Import through File → Import and untick “Convert text to numbers, dates and formulas” before clicking Import data. That checkbox keeps every cell as text, so 00501 stays 00501. If you are pasting into an existing sheet, set the target cells to Format → Number → Plain text first.
Why does Google Sheets turn my long numbers into 1.2E+11?
By default Sheets treats any numeric-looking cell as a number and shows large numbers in scientific notation, so a 12-digit barcode like 885909560622 becomes 8.85909E+11. Importing with conversion switched off keeps it as the literal digit string.
Can I get the leading zeros back after they are already gone?
If the value was only shortened, yes — re-pad it to its known width with =TEXT(A2,"00000"), or run the original CSV through a padding tool before re-importing. If a 16+ digit number was truncated to 15 significant digits, the lost digits are gone and you’ll need to re-export from the source system.
Does changing the column to Plain text fix numbers that already lost their zeros?
No. Formatting a column as Plain text after the import only changes how the already-damaged value is displayed. The zeros were dropped when the value was parsed as a number, so you have to set Plain text before the data enters the cell, or re-import with conversion turned off.
Sources: Google’s import conversion behaviour is documented across the Google Docs Editors community. The 15-significant-digit limit is a property of IEEE 754 double-precision floating point, used by both Sheets and Excel.