How to Add Leading Zeros in Excel (and stop Excel removing them)

Type 00042 and Excel shows 42. It treats the entry as a number, and numbers don’t have leading zeros. Four ways to keep them:

1. Custom number format (best for IDs you still sort as numbers)

1. Select the cells › Ctrl+1.
2. Custom › Type: 00000 (one 0 per digit you want).
3. OK.

42 now shows as 00042; the value is still 42. See custom number format.

2. TEXT function (when you need it as text)

=TEXT(A2,"00000")

Returns “00042” as real text — needed when joining it with other text or exporting. See TEXT.

3. An apostrophe before typing

Type '00042. The apostrophe doesn’t show; the cell keeps the zeros as text. Good for a few entries.

4. Format as Text before typing

Select the column › Home › Number format box › Text › then type or paste. Everything you enter stays exactly as typed. Use this for phone numbers, account numbers and anything longer than 15 digits (Excel rounds longer numbers).

Pad to a length when lengths vary

=RIGHT("000000"&A2,6)

Makes every code 6 characters: 42 → 000042, 12345 → 012345.

Zeros vanish when opening a CSV

Double-clicking a CSV lets Excel strip them. Import it instead with Data › From Text/CSV and set the column to Text. See open a CSV correctly.

Related: number stored as text