2 Methods: Adding Leading Zeros (0) in Excel using Functions!

How Can You Add Leading Zeros to Any Numbers in Excel?

Narendhiran Vijayakumar 44 seconds agoLast Updated: June 11, 2025

0 0 2 minutes read

In Excel, you can’t enter a zero at the infront or beginning of a number. For example, if you try to type “0012345” in a cell, Excel will automatically remove the leading zeros and show it as “12345“. That’s because, by default, Excel sees leading zeros as unnecessary in numbers. But in some cases, like when you’re working with zip codes, product IDs, or any other data that must have those important zeros at the beginning, you need to add them back. Here in the tutorial, I’ll share you the two effective methods of adding leading zeros in Excel using default functions.

Excel won't allow you to add zeros upfront to any numbers — Excel won’t allow you to add zeros upfront to any numbers

In this tutorial, You can learn to add leading zeros

To make numbers the same length.
To numbers or text

Method 1: Add Leading Zeros to Make Numbers the Same Length

What is meant by “make numbers the same length”? I mean adjusting all the numbers in a list or column so they have the same total number of digits. For example, if you have numbers like 23, 456, and 7, and you want them to all have five digits, You might need to add zeros in front of them to get the output as “00023, 00456, 00007“.

You can achieve this via two methods, one is using the TEXT function and another one is by using formatting cells option.

TEXT Function to Add Leading Zeros:

The TEXT function will helps you to return the numbers to a fixed-length format aligning with leading zeros. You can use the following Excel formula,

=TEXT(B4,"0000000") // You can change the number of zeros based on your need.

Added leading zeros using TEXT function to make numbers the same length.

From the above image, you can see that the TEXT function added the leading zeros to make the numbers same length.

Using Cell Formatting Options to Add Leading Zeros:

Firstly, you need to select the range of cell values.
Right click on the cells and choose the Format Cells option.

Right click on the cells and choose Format Cells... option — Right click on the cells and choose Format Cells… option

Now in the format cells dialog box, you need to choose the Custom option.
In the type, you need to choose the value “0“.

Now, you need to replace the single “0” value to “0000000” value based on your needs.
Once you’re done, you need to click the Ok button to complete the formatting.

That’s it, Now the leading zeros has been added with the same length.

Leading Zeros with same length of the numbers has been added using cell formatting.

Method 2: Adding Fixed Number of Leading Zeros

Unlike the TEXT function, here CONCATENATE function is used to add the fixed number of leader zeros. CONCATENATE function simply adds the value to any given numbers or text value and so it returns the fixed value.

To add three “000” leading zeros using CONCATENATE function, use the following formula:

 =CONCATENATE("000", B3) // You can change the number of 0's in the formula

Adding leading zeros with CONCATENATE function.

That’s it.

–Narendhiran Vijayakumar.

Feel free to comment us below, if you have any queries about the above topic and find more interesting excel tutorials on our homepage: Excel24x7.com.