How to Add or Keep Leading Zeros in Excel

To add leading zeros in Excel, you either turn the number into padded text with =TEXT(A2,"00000") or tell Excel to display zeros with a custom number format. Excel removes the zeros because it reads 00742 as the number 742. This guide shows four ways to add or keep them, and which to use for IDs, ZIP codes and phone numbers.

Why does Excel remove leading zeros?#

Excel removes leading zeros because it treats anything that looks like a number as a number. Mathematically, 00742 and 742 are the same, so Excel stores 742 and throws the zeros away.

That's fine for prices and quantities, but wrong for codes. An employee ID, a product code, a US ZIP code like 02134 or a phone number is really a label made of digits. You never add them up, and the zeros matter.

So the fix is one of two things:

• Store the value as text, so Excel keeps every character you type.
• Keep it a number, but use a format that displays the zeros.

How to add leading zeros with the TEXT function#

Use TEXT with a format code made of zeros, one for each digit you want. For five-digit IDs:

=TEXT(A2,"00000")

Each 0 in the format code means "show a digit here, or a zero if there isn't one". So:

A number longer than the format code isn't cut. =TEXT(123456,"00000") returns 123456.

TEXT always returns text. That's what you want when you join the code to other text, such as ="EMP-"&TEXT(A2,"00000") for EMP-00742. It also helps when you need to match these IDs against a list stored as text. The TEXT function guide lists more format codes, and the guide to adding text to the start or end of cells shows more ways to build codes like this.

How to show leading zeros with a custom number format#

A custom number format displays the zeros but keeps the value a real number. To apply one:

1. Select the cells.
2. Open Format Cells (Windows: Ctrl+1; Mac: ⌘1).
3. On the Number tab, choose Custom.
4. In the Type box, enter 00000 and click OK.

The cell now shows 00742, but the formula bar still shows 742. Sorting, SUM and lookups against other numbers all keep working, because nothing about the value changed.

The catch: the zeros exist only on screen. If you copy the cells into another program or export to CSV, you may get 742 again. And a lookup against text IDs like "00742" won't match a number 742. If the zeros must travel with the data, convert to text with TEXT.

How to keep leading zeros when you type#

Start the entry with an apostrophe, or format the cells as Text before you type. Both tell Excel to keep exactly what you enter.

• Apostrophe: type '00742. The cell shows 00742 and the apostrophe stays hidden. It only appears in the formula bar.
• Text format: select the column, choose Home > Number Format > Text, then type. This is better when you'll type many codes.

Formatting a column as Text after typing doesn't bring back the zeros. Excel already stored 742, so you'd have to retype the values.

Excel may show a small green triangle with the warning "Number stored as text". For codes, that's expected. Choose Ignore Error if it bothers you. The comparison of TEXT and VALUE explains when you want text and when you want numbers.

Recent Microsoft 365 versions also have File > Options > Data > Automatic data conversion, where you can turn off removing leading zeros for new data. On a shared file, don't rely on everyone having that setting.

How to pad codes that are already text#

When codes arrive as text of different lengths, join zeros to the front and keep the last few characters:

=RIGHT("00000"&A2,5)

For A2 = 742, the join makes 00000742, and RIGHT keeps the last five characters: 00742. This works whether A2 holds a number or text, and it's handy for mixed data.

Another option is REPT, which repeats a character a set number of times:

=REPT("0",5-LEN(A2))&A2

For 742, 5-LEN(A2) is 2, so it adds two zeros. Be careful: if A2 is already longer than five characters, the count goes negative and REPT returns #VALUE!. The RIGHT version just cuts to five characters instead.

How to keep leading zeros when opening a CSV file#

Import the file instead of opening it, and set the ID column to Text. When you double-click a CSV, Excel converts every number-looking column on its own and the zeros are gone before you see them.

1. Go to Data > From Text/CSV and pick the file.
2. In the preview, click Transform Data.
3. Click the ID column's type icon in the header and choose Text.
4. Click Close & Load.

The zeros stay because Excel loads the column as text from the start. The same steps help with long card or account numbers, which Excel would otherwise round after 15 digits.

Practise it#

Knowing when a value should be text and when it should be a number is a skill job tests check often. Practise TEXT and VALUE in a live spreadsheet: you type the formulas and get instant feedback. Then see how the same format codes help you combine a date with text, or try the exercise on this page first.