Add Leading Zeros in Excel

When you create a form you may want to include zeros in front of the number. For example

000123

But after you enter this value and press Enter it will immediately change to

123

This happens because the default data type of a cell is general. This means that Excel is trying to guess which data type you are trying to use.

In this example, it treats it as a number because all characters inside cell B2 are numbers.

Forcing Excel to treat the value as text

If you want Excel to treat a value as text and not as a number, function, or any other value, insert (’) at the beginning of the phrase.

Instead of writing 000123

write ‘000123

When you try to use the value from cell A2 to add numbers it will be treated as a number.

Custom formatting

If you already inserted the numbers to which you want to add leading zeros, you can use the custom formatting.

First, select the numbers.

Use right-click to open the contextual menu (Ctrl + 1). Go to Custom and enter the desired number of zeros.

For example, if you enter 000000 then in the cell where you have 123 you will have 000123.

This is the result.

Because the value in cell A6 is longer than the number of zeros – 1, it won’t add any leading zeros.

TIP

You can format cells before entering numbers. In this case, the number 123 will automatically change to 000123.

Text function

Instead of custom formatting, you can use the TEXT function. This function will convert a value to text using the specified number format.

Let’s simulate an earlier example with this function using the following formula.

This will give us the following result.

Concatenate function

If you want to add a specified number of leading zeros, you can use the CONCATENATE function.

The following formula will add 3 leading zeros to every value.

The problem with this function is that it will add three zeros also to 0.

Let’s modify this formula a bit.

This formula will add zeros only if a value is different than 0.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.