How to add zero before number in excel (11 ways)

While working in Excel sometimes you need to be a little tricky. Like, if you want to add leading zeros in excel cells containing numbers, normally it is not possible. But there are a few tricks that can help you to do so. In this article, I will guide you on How to add zero before number in excel.

Identify the problem first

If you type a number leading with a zero in MS Excel, Excel will omit the zero from the left side as it does not increase the value of that number. See the below screenshot. But in some cases, we need to show the zero in front of the number.

In this article, I will share some tricks to add zero before any number in MS Excel.

Read More: How to insert check box in Excel

how to add zero before a number in excel

There are many tricks to add zero before any number in MS Excel. I will share with you 11 tricks in my article.

1. Add an apostrophe (‘) symbol

Using an apostrophe (‘) symbol is the most popular way to add a zero before a number. if you want to type 01714171769 then just type ‘01714171769. It will display the leading zero before the number in the cell.

Maybe you can see an error (a green dot in the top left corner of the cell). To avoid the error just right-click on the error and select ignore the error.

2. Add a hyphen (-)

You can add a hyphen (-) symbol at any place of the number, it will also work to add a zero in the leading position of the number. If it is a mobile phone number or a bank account number, you can easily add a hyphen symbol to separate it. See the screenshot below to understand.

3. Add space ( )

It is another tricky way to add zero before any number. If you are typing a mobile number, bank account number, or identity number you can group every three or four digits and add space between them. You can add as many zeros as you need before the number.

4. Change the cell format

By default the format of all cells in MS Excel is General but when you need to add a zero to a leading position of a number you can change the cell format to Text. Then you can add a zero before the number in Excel.

If you do not know how to change the cell format, right-click over the cell, and select Format Cells. Go to the Number tab, select Text from the list and click on OK.

5. Custom Format

Right-click over the cell and select Format Cells.

Select the Number tab, select Custom from the list, and type 00000000000 (11-digit zero). if your number is 4 digits including zero type four zeros.

Now a zero will be added to the leading position of the number automatically.

6. Using the TEXT function

The TEXT function can help you to add a leading zero to a number as we know TEXT function can change the format of a cell.

7. Using Ampersand (&) symbol

You can take the help of an ampersand (&) function to add a leading zero to a number.

8. Using the CONCAT function

The CONCAT function also can be used to add a zero in the leading position of a number.

9. Using the BASE function

The BSAE function converts a number into a text representation with the given radix (base). So, you can BASE function to add a leading zero to a number.

10. Using the RIGHT function

You can use the RIGHT function to add a zero before a number in excel. The RIGHT function will extract the right-most N characters from a text value.

11. Using the REPT function

The REPT function allows any text repetition at any time. If I want to repeat zero one time before the number then I can use this function.

Dear visitor, hope this article on how to add zero before number in excel is helpful to you. If you have any more queries please feel free to post a comment below. I will try my best to reply to your question as soon as possible. Thanks a lot for being with us.