Why Excel Deletes Leading Zeros from CSV Files (and How to Stop It)

A practical guide — Excel, Google Sheets, Python, and the automatic way.

The file is fine. Open your CSV in a text editor and there they are: 00127, 045-9982, the full 18-digit order IDs. Double-click it, though, and Excel "helpfully" decides that column is a number: 00127 becomes 127, and anything over 15 digits gets rewritten in scientific notation — 348201774521003987 turns into 3.48202E+17, with the last digits permanently replaced by zeros. The damage isn't in the file. It happens in the half-second between Excel opening the file and drawing the grid, when it guesses each column's data type. The fix is to stop letting Excel guess.

The one rule: never double-click a CSV to open it in Excel. Always import it. Double-clicking runs the auto-guess on every column with no chance to intervene.

Method 1: Excel import wizard (set the column to Text)

  1. In a blank workbook, go to Data → From Text/CSV and select your file.
  2. In the preview dialog, click Transform Data (this opens Power Query instead of loading straight away).
  3. Select the column with your codes, then go to Transform → Data Type → Text. Repeat for every ID/code column.
  4. Home → Close & Load. Leading zeros intact, long IDs preserved as text.

Shortcut when you only need one column fixed: in the import preview, click the column header and change its Data Type to Text directly, then Load — same result, fewer clicks.

Method 2: Pre-format a blank sheet as Text, then paste or open

  1. Open a blank workbook. Select the column(s) that will hold codes, right-click, Format Cells → Text → OK.
  2. With the file's contents on your clipboard (or via Data → From Text/CSV into the pre-formatted sheet), paste into A1.

Excel respects the existing format for pasted and imported text — the zeros survive. This is the fastest rescue when you've already got the data in another window.

Method 3: Google Sheets

  1. File → Import → Upload, pick your CSV.
  2. In the import dialog, find Convert text to numbers, dates and formulas and set it to No.
  3. Choose your import location and click Import data.

Sheets then keeps every value exactly as written in the file — zeros, long IDs and all.

Method 4: Python (for the technical)

import csv
import pandas as pd

# Read every column as text — nothing gets reinterpreted.
df = pd.read_csv('orders.csv', dtype=str)

# ... work with df ...
df.to_csv('orders_fixed.csv', index=False, quoting=csv.QUOTE_MINIMAL)

# Or without pandas: the csv module keeps all values as strings by default.
with open('orders.csv', newline='', encoding='utf-8') as f:
    rows = list(csv.reader(f))
# rows are plain strings here; zeros were never touched.

What about damage that's already done?

If the only copy you have was saved after Excel mangled it, the zeros are unrecoverable from the file alone — 127 gives you no way to know whether it was 00127 or 0127. Go back to the original export. If the IDs are fixed-width (always 5 digits, say), you can rebuild them with a formula like =TEXT(A2,"00000") — but only when you're certain of the width.

Scope note: leading zeros are destroyed at open time, not stored wrong in the file — so this guide is about the import, and no cleaner can rewrite Excel's auto-guess. What a cleaner can do is make sure the file Excel imports has no other surprises: stray spaces that break the Text-type match, ragged rows that shift IDs into the wrong column, or mixed encodings that turn codes into mojibake. Fix the import with the steps above; fix the file's other messes below.

The one-click way

Most mangled CSVs start life as messy exports — ragged rows, stray spaces, mixed encodings — and every tool they pass through makes its own guesses about your data. CSV Cleaner trims every cell, normalizes headers, strips blank rows, and converts to clean UTF-8 in one click, so the file Excel imports has nothing left to misinterpret. $9 once, yours forever.

See CSV Cleaner

More CSV fixes: Why Your CSV Shows Garbage Characters in Excel · How to Fix Numbers Stored as Text in CSV Files · CSV Cleaner

← All guides