Pad Numbers with Leading Zeros

All versionsTEXT

To force numbers to a fixed width with leading zeros — turning 42 into 00042 for IDs, SKUs, or ZIP codes — use the TEXT function with a 0 format. The result is text, which is exactly what an identifier should be.

Quick formula: to format the number in A2 as a 5-digit, zero-padded string:

=TEXT(A2, "00000")

Each 0 is a required digit slot; numbers shorter than the pattern get leading zeros, so 42 becomes “00042.”

Functions used (tap for the full reference guide):

TEXT REPT

The example

IDs padded to 5 digits.

	A	B
1	ID	Padded
2	42	00042
3	1750	01750
4	9	00009

The formula

The padded ID in B2:

=TEXT(A2, "00000") // 42 → "00042"

How it works

The format code defines the width:

In a TEXT format, each 0 is a digit placeholder that always shows — padding with a zero if there’s no digit there.
"00000" means “at least 5 digits,” so 42 gets three leading zeros → "00042".
Numbers already 5+ digits are shown in full — nothing is truncated.
The output is text, which is correct for IDs (you don’t do math on a ZIP code).

Just want it to display padded while staying a real number? Use a custom cell format (Ctrl+1 → Custom → 00000) instead of TEXT — the underlying value stays numeric.

Try it: interactive demo

Live demo

Enter a number and a width.

Number (A2)

Width

Padded:

Variations

Pad to a width with REPT

Alternative that pads any text, not just numbers:

=RIGHT(REPT("0",5) & A2, 5)

Add a fixed prefix

Build a code like INV-00042:

="INV-" & TEXT(A2, "00000")

Display-only padding (keep the number)

Custom number format, not a formula:

Format Cells → Custom → 00000

Pitfalls & errors

TEXT returns text. You can’t sum or do math on the result. If you need a real number that merely looks padded, use a custom cell format instead.

Leading zeros vanish on import. If a CSV column of IDs lost its zeros, the original numbers were stored numerically — re-pad with TEXT, or import the column as text.

Width too small truncates nothing — but be sure your pattern has enough 0s for your largest value, or longer numbers simply show in full (no padding), which may misalign columns.

Practice workbook

📊

Download the free Pad Numbers with Leading Zeros practice workbook

IDs with live TEXT zero-padding, the REPT and prefix variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I add leading zeros to numbers in Excel?

Use =TEXT(A2, "00000") to pad to five digits. Each 0 is a required digit, so shorter numbers gain leading zeros. The result is text.

How do I keep leading zeros but still have a number?

Apply a custom cell format (Ctrl+1 > Custom > 00000) instead of TEXT. The cell displays padded while the underlying value stays numeric.

Why did my IDs lose their leading zeros?

They were stored as numbers, which drop leading zeros. Re-pad them with TEXT, or import the column as text so Excel keeps the zeros.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: TEXT · REPT