How to Add Leading Zeros in Excel and Keep Them There

Type a stock code that starts with a zero into a cell and Excel quietly strips the zero off. Microsoft explains why: Excel removes leading zeros so that formulas and maths work on the value. That's fine for quantities, and a nuisance for stock codes, order references, staff numbers and postcodes that are meant to keep their zeros. There are four ways round it, and which one you pick depends on whether the zeros have already gone.

Four ways to keep or add the zeros

Method	How	Stored as	Good for
Custom number format	Ctrl+1, Custom, type 00000	Number	Codes you won't do maths on but want sorted as numbers
Apostrophe	Type '00123	Text	A few cells typed by hand
Format as Text first	Ctrl+1, Text, then type	Text	A column you'll fill in later
TEXT function	=TEXT(A2,"00000")	Text	Fixing a column that's already lost its zeros

Using a custom number format

1. Select the cells holding your codes.
2. Press Ctrl+1 to open Format Cells.
3. On the Number tab, choose Custom from the Category list.
4. In the Type box, type one zero for each digit the code should have. For five-digit codes, type 00000.
5. Select OK. Codes you type into those cells now show with zeros in front to make up five digits.

Microsoft gives 00000 as its example for a five-digit postal code. The value underneath is still the plain number, which is why this method keeps sorting and lookups working on numbers. It also works for codes with fewer than 16 digits, Microsoft says.

Typing an apostrophe in front

For the odd code typed by hand, put an apostrophe first: '00123. Microsoft says Excel then treats the entry as text and keeps the zeros. The apostrophe doesn't show in the cell, only in the formula bar. You might see a small green triangle warning that the number is stored as text; for a code, that's exactly what you want, so you can ignore it.

Formatting a column as Text before you type

If you're setting up a new sheet for order references, select the column, press Ctrl+1 and choose Text. Anything you type afterwards keeps its zeros. It's the cleanest option for a column of codes that will never be added up.

The catch: formatting won't bring zeros back

This is what trips most people up. Microsoft says a custom format, or a Text format, doesn't restore leading zeros that were removed before you applied it. It only affects numbers entered afterwards. So if Excel has already stripped the zeros from a code, changing the cell to Text afterwards won't put them back.

If you need the zeros as part of the actual text, for example to export the codes or match them against another list, the TEXT function is the dependable route, because it creates a new text value with the zeros built in.

Fixing codes that have already lost their zeros

1. Insert an empty column next to the codes.
2. In the first cell, type =TEXT(A2,"00000"), using as many zeros as the full code length.
3. Copy the formula down.
4. Copy the new column and use Paste Values to keep the results as plain text.

Microsoft gives this exact approach for postal codes: =TEXT(A1,"00000") gives 00123 for a five-digit postal code.

Long codes and imported files

Excel only keeps 15 significant digits, Microsoft says. Codes of 16 digits or more lose their last digits unless you store them as text, so long reference numbers should always go into a Text column or start with an apostrophe.

For CSV files, the zeros often disappear the moment Excel opens the file. Microsoft's suggestion is to import the file with Data, From Text/CSV and set the code column to Text in the Power Query editor before loading. Newer versions of Excel, including Excel for Microsoft 365 and Excel 2024, also have an Automatic Data Conversions setting that can stop Excel removing leading zeros in the first place.

If people keep typing codes in different ways, a drop-down list in Excel stops the variations at source. And when you're working out stock per code once the list is clean, my guide to dividing in Excel picks up from there.

Checking which method a sheet already uses

If you've inherited a sheet of codes, click one and look at the formula bar. If the bar shows the code without its zeros but the cell shows them, it's a custom number format. If the bar shows an apostrophe or the full code with zeros, it's stored as text. That matters for lookups: a code stored as a number won't match the same code stored as text in another list, even though they look identical on screen. Pick one approach per column and stick to it, and your lookups and counts will behave.

It's also worth adding a quick note in the header, such as Code (text), so whoever edits the sheet next knows not to change the format. Small labels like that save a surprising amount of confusion on shared sheets.

What I'd do

For a new list of codes, format the column as Text before typing anything. For an existing column, use a custom 00000 format if you only need the look, or the TEXT function if you need real zeros. And never trust a CSV to keep its zeros; import it through Data, From Text/CSV instead.

Frequently asked questions

Why does Excel remove leading zeros?

Excel treats the entry as a number so formulas and maths can work on it, and numbers don't keep leading zeros.

How do I keep leading zeros when typing in Excel?

Type an apostrophe first, such as '00123, or format the cells as Text before you type.

How do I add leading zeros to existing numbers?

Use a custom number format such as 00000, or =TEXT(A2,"00000") in a spare column.

Why does formatting as Text not bring back my zeros?

Microsoft says formatting doesn't restore zeros removed before the format was applied. Use the TEXT function instead.