How To Put 0 In Excel In Front Of Numbers

When you input a number in an excel cell and if the number needs to start with 0, you will find excel will remove all the zeros at the beginning of the number. In this article, I will tell you how to put 0 in excel in front of numbers with several methods.

1. Use Apostrophe To Put 0 In Excel In Front Of Numbers.

1. Input the number 001 in cell A1.
2. When you press the enter key, it will display the number 1 in cell A1.
3. If you want to reserve the 2 zeros before the number 1, you should first input the apostrophe character ( ‘ ) before 0.
4. Now input the text ‘001 in cell A1.
5. When you press the enter key now, it will display the number 001 in cell A1 also.

2. Format Cells To Put 0 Before Numbers In Excel.

1. If you have multiple numbers in an excel column like below, and you want to put a fixed number of 0 in front of each number, you can use the format cell feature.
   
2. Select all the above number cells, right-click them and click the Format Cells… item in the popup menu list.
3. In the popup Format Cells dialog, click the Number tab.
4. Click the Custom item in the Category list.
5. Input the text 000### in the Type input box on the right side.
6. Click the OK button to save the changes.
7. Now you can see the numbers have been changed as below.
   
8. You will find that it does not add 0 in front of each number, this is because the total number of digits in the cell format text is 6 ( 000### ).
9. So if the original number contains less than 6 digits, it will add the related zeros in front of the original number.
10. If the original number contains more than 6 digits, it will display the original number.
11. So to put a fixed number of 0 in front of all the numbers, you should use the custom cell text format 000###### in this example.
12. Then you will get the below cell data.
    

3. How To Put 0 In Excel In Front Of Numbers To Make All The Numbers The Same Length.

1. In the above example, the result number’s length is not the same after adding the zeros before the number.
2. Now I will tell you how to make the numbers the same length after adding zeros before the number.
3. Select the number column, right-click it and click the Format Cells… menu item in the popup menu list.
4. Select the Custom item in the Number tab Category list.
5. Then input the text format 000000000 in the Type input box on the dialog right side.
6. Click the OK button to save it.
7. Then you will get the below cell data, you can see all the numbers are the same length and start with zeros.
   

4. How To Use Formula To Add Fixed Number Of 0 In Front Of Number.

1. We can also use an excel formula to add a fixed number of 0 in front of numbers, below is the example data cells.
   
2. Input the formula =IF(ISNUMBER(A1)=TRUE,”000″&A1,””) in cell B1.
3. When you press the enter key, it will display the number 000123 in cell B1.
4. Select cell B1 and drag down the small cross icon on the bottom right corner of cell B1 to cell B3.
5. Then it will autofill the formula to all the 3 excel cells.
6. Then you will get the below data cells.
   

5. How To Use Excel Formula To Add 0 In Front Of Number And Make The Result Number The Same Length.

1. You can also use an excel formula to add 0 in front of numbers and make the resulting number the same length.
2. Input the formula =TEXT(A1,”000000000″) in cell B1.
3. Then autofill the formula to cell range B1:B3, then you will get the below data cells.