Why do my leading zeros keep disappearing?

Because Excel treats the cell as a number, and numbers have no leading zeros. A custom format only changes how the number looks; the moment you export to CSV or use the value elsewhere, the padding is gone. Store the value as text (TEXT function or Tellsheet's Leading Zeros) to keep them.

Home › Excel Guides › Add Leading Zeros in Excel

How to Add Leading Zeros in Excel

Zip codes, SKUs, and IDs often need a fixed width — 42 should read 00042. The catch: a number format makes the zeros look right but drops them on export. Here's how to make them stick.

Last updated: August 2026

Value		Padded to 5
42	→	00042
1234	→	01234
7	→	00007

Method 1 — custom number format (display only)

Select the cells and press Ctrl+1.
Custom, then type 00000 (one zero per digit you want).

Quick, but it only changes how the number looks. Export to CSV or reference the value elsewhere and the padding is gone — the real value is still 42.

Method 2 — the TEXT function (real text)

In a new column: =TEXT(A2, "00000") → real text 00042.
Copy, then Paste Special > Values to lock it in.

These zeros survive CSV export and lookups, because the cell is genuinely text now.

The 1-click way — Tellsheet

Leading Zeros pads a whole column to the length you set — say 5 digits, so 42 → 00042 — as real text that keeps its zeros in CSVs and lookups. Need the opposite? Switch to remove mode to strip leading zeros from the column. Every change previews first, and it runs entirely on your computer. Pairs with Convert Text to Number and Clean.

Frequently asked questions

How do I add leading zeros in Excel?

A custom format (00000) pads the display only; =TEXT(A2,"00000") makes real text that keeps the zeros. Tellsheet's Leading Zeros pads a whole column as real text in one click.

Why do my leading zeros disappear?

Excel stores the cell as a number, and numbers have no leading zeros. A format only changes the look; store the value as text to keep the padding.

How do I remove leading zeros?

=VALUE(A2) or multiply by 1 turns it back into a number and drops them. Leading Zeros has a remove mode for the whole column.

Will the zeros survive a CSV export?

Only if the value is real text. Number-format padding won't; the TEXT function and Tellsheet's Leading Zeros will.

Related Excel guides

Pad a column in one click

Leading Zeros pads every value to a fixed width as real text — or strips zeros back off — right inside Excel.

Get Tellsheet free See pricing