Add leading zeros to numbers

Sample data. IDs typed as numbers in column B, the formula in C2 is copied down.

The formula

Click the first ID cell (C2 here) and type:

=TEXT(B2,"00000")

C2 shows 00021. Copy it down: 8090 becomes 08090 and 7 becomes 00007.

• B2 is the number to pad.
• "00000" is the pattern: each 0 is one digit, and any digit the number does not have is filled with a zero.

Use as many zeros as the length you want: "000" turns 21 into 021. A number longer than the pattern is not cut, so 123456 stays 123456.

Keep it a number: use a custom format

TEXT gives you text, so the result is no longer a number. If you only want the zeros to show, keep the number and change how it is displayed:

• Excel: select the cells, press Ctrl+1, choose Custom and type 00000 in the Type box.
• Google Sheets: Format, Number, Custom number format, type 00000, Apply.

The cell shows 00021 but still holds 21, so sums and lookups against other numbers keep working.

Codes with letters, or a mix

TEXT only pads numbers. For codes that can contain letters, join zeros in front and keep the last five characters:

=RIGHT("00000"&B2,5)

21 becomes 00021 and AB becomes 000AB. One catch: RIGHT cuts anything longer than five, so 123456 comes back as 23456. Use TEXT when the numbers can be longer than the pattern.

Add one zero in front, whatever the length

Join a zero to the number with &:

="0"&B2

21 becomes 021 and 8090 becomes 08090. The result is text, like the TEXT version.

Type it with the zeros

To type 00021 and keep it, start with an apostrophe: '00021. The apostrophe does not show in the cell and the entry is stored as text. You can also set the column to text before you type (Excel: Ctrl+1, Text; Google Sheets: Format, Number, Plain text).

Excel marks text that looks like a number with a small green triangle in the corner. It is a warning, not an error.

When a CSV file loses its zeros

Opening a CSV file in Excel turns 00021 into 21 as it loads. In Microsoft 365 you can switch that off once: File, Options, Data, Automatic Data Conversion, and untick Remove leading zeros and convert to a number (on a Mac: Excel, Preferences, Edit). Zeros already lost can be put back with the TEXT formula above.

Turn it back into a number

VALUE turns the padded text back into a number:

=VALUE(C2)

00021 comes back as 21. TEXT, RIGHT and VALUE work in every version of Excel and in Google Sheets.

Templates with the math already done

Rather skip the formulas? These templates come with every formula built and checked, plus a filled-in sample.