While working on an Excel worksheet, you may see that Excel doesn’t allow 0 before any numbers. So, you can’t make a column with the same number of digits. If you just put a 0 before other digits and press Enter, Excel will not show the 0 but the other digits only. But there are some easy and tricky methods to put 0 in Excel in front of numbers. In this article, you will learn 5 of them.
How to Put 0 in Excel in Front of Numbers: 5 Effective Methods
Suppose you have some data of numbers which are of different digits. But, you want to make the numbers of the same digits to make the worksheet look good. For this, you want to add several 0 in front of the numbers. Here, I am showing you 5 methods to put 0 in front of numbers in Excel.
1. Convert Numbers to Text Format to Add Leading 0 in Excel
If you convert numbers format to text, then you can type as many leading zeroes as you want. Excel will not vanish them. Follow the steps.
📌 STEPS:
- Simply, you can use an Apostrophe(‘) before starting a number to add 0 at the front. For this, just start the number with an Apostrophe. But, the Apostrophe will not show in the cell also there will be an error warning.
Similarly, put an Apostrophe in all cells and add the necessary zero as you want.
💬 NOTES:
- Adding an Apostrophe at the front will convert the number to Text format. So, you can’t use them for any calculation,
- There, in the top left corner of the cells, you see a green tiny arrow symbol. After clicking on the arrow, a message will come saying “Number stored as text”. To continue as it is, click on the option “Ignore Error”
- Using this method will not automate the other cells. You have to add Apostrophe and 0 in all cells manually.
You also can do it directly. Follow the steps below.
- Firstly, select the cells of the column.
- Then, go to the Number tab on the top ribbon. And open the drop-down menu.
- From the format options, select the Text option.
- As an outcome, the cells will become in Text format.
- Then, you can add necessary zeros in front of numbers.
💬 NOTE:
- By this method, you convert the numbers to Text format. So, you can’t use them for any calculation.
Read More: How to Add Leading Zeros in Excel Text Format
2. Use Custom Formatting
Alternatively, you can use the Custom Format option to make the format of the cell as you want. To do this, follow the steps below.
📌 STEPS:
- First, select the cells containing the numbers.
- Then, go to the top ribbon and in the Number Tab, you find an arrow in the bottom right corner of the tab. Then press on the arrow. See screenshot.
- As a response, there will be a window named ‘Format Cells’.
- Then, stay in the Number tab and go to the Custom option.
- And, in the Type box, write ‘0000′ (only zeros, not the quotes) to make a 4-digit number. Excel will add the necessary 0 in front to make the numbers of 4 digits.
- Finally, press the OK button.
- As a result, you will see the Output column numbers have become 4 digits and there are necessary 0’s in front.
💬 NOTES:
- By this method, the numbers will remain in Number format. So, you can do any calculations with them.
- If you do copy and paste them as values, it will lose the 0’s in front.
- In the formula bar, you will see the original value without any 0’s in front.
Read More: How to Add Leading Zeros to Make 10 Digits in Excel
3. Utilize the Ampersand (&) Symbol
In Excel, you can use the Ampersand symbol to add to values of two cells but in Text format. So, you can use this to add extra 0’s in front of the numbers. To do this, follow the steps below:
📌 STEPS:
- First, write this in cell C5:
="00"&B5- So, it will add two zeros in front of the number of cell B5.
- Now, copy the formula and paste it into other cells to do the same thing. Also, you can use the Fill handle button.
💬 NOTES:
- By this method, you convert the numbers to Text format. So, you can’t use them for any calculation.
- And, it will add 2 zeros to all cells, so it will not make numbers of the same digits.
4. Utilize Excel Functions to Get 0 in Front of Numbers
In Excel, there is an enormous number of functions that have made our work easier. So, we can use some function to add 0 in front of numbers in Excel.
4.1 Using REPT Function
The purpose of the REPT function is to repeat a text or character a specified number of times.
Syntax:
=REPT (text, number_times)
Text: The text or character that will be repeated
Number_times: It is the number of times to repeat the text.
📌 STEPS:
- First, input this formula in cell C5.
=REPT(0,2)&B5
- Then, copy and paste the formula into the other cells.
- As a result, two zeros will be added in front of all the numbers.
- So, It will not make numbers of the same digits.
To make numbers of the same digits, you will have to use the IF and LEN functions together with the REPT function. Follow the steps below:
- At first, write this formula into cell C5.
=IF((LEN(B5)<6),REPT(0,6-LEN(B5))&B5,B5)
🔎 Formula Explanation:
- LEN(B5): It calculates the number of characters in cell B5
- LEN(B5)<6: It is the condition that if the number of characters in cell B5 is less than 6
- 6-LEN(B5): It will give the value that is needed to make the number of 6 digits.
- REPT(0,6-LEN(B5))&B5: It will add the required zeros in front with cell B5
- =IF((LEN(B5)<6),REPT(0,6-LEN(B5))&B5,B5): If the B5 cell value is less than 6 digits then it will add the required digits to make 6 in front of the number else it will give the value of cell B5.
- Finally, copy and paste the formula to other cells using the Fill Handle icon or shortcuts.
💬 NOTES:
- Using only the REPT function will only add digits in front of the number but it will make all the numbers of a required digit.
- However, using the IF function with the REPT function will allow you to make the cells of the same number of digits.
- As the cells remain in Number format, you can use them for any calculations.
4.2 Using TEXT Function
Also, you can use the TEXT function to convert the number to Text format and of specified digits.
Syntax
=Text(Value, format_text)
Value: It is the number that you will convert to text
Format_text: It is the format that you want to use.
📌 STEPS:
- For this, write this formula into cell C5
=TEXT(B5,"0000")- So, it will convert the value of cell B5 to Text format and will add the necessary 0’s to make it 4 digits.
- Then, copy and paste the formula to other cells using the Fill Handle icon or shortcuts Ctrl + C and Ctrl + V.
4.3 Using CONCAT (or CONCATENATE) Function
You can use the CONCAT or CONCATENATE function to merge or add the characters of two cells. So, you can use this function to add 0’s in front of the cells.
📌 STEPS:
- For this, write the following formula in the cell C5
=CONCAT("00",B5)
- Then, use the Fill Handle icon to copy and paste the formula to the other cells.
💬 NOTE:
Using the CONCAT function, it will just add two zeros to the cells so it will not make the cells of the same digit.
4.4 Using RIGHT Function
Generally, we use the RIGHT function to take certain digits of a cell from the right. So, to add 0 in front of numbers to make them of any specified digits, you can use the Right formula.
Syntax
=RIGHT (text, [num_chars])
Text: From which you will extract characters from the right.
Num_chars: It is the number of characters that will be extracted from the right
Follow the steps below:
📌 STEPS:
- Firstly, write the following formula in cell C5.
=RIGHT("00"&B5,4)
- Then, use the Fill Handle icon to copy and paste the formula to the other cells.
4.5 Using BASE Function
We use the BASE function to convert numbers to another base. To use this function follow these steps.
Syntax:
=BASE (number, radix, [min_length])
Number: It is the number that will be converted. It must be an integer value and >= 0.
Radix: It is the base radix to which the number will convert. it must be >=2 or <=36.
Min_length: It is the minimum length of the string
📌 STEPS:
- Firstly, write the following formula in cell C5.
=BASE(B5,10,4)
- Then, use the Fill Handle icon to copy and paste the formula to the other cells.
Read More: How to Add Leading Zeros in Excel
5. Use the Power Query Tool
We use the Power Query to transform data in Excel. So, it is possible to add 0 in front of numbers. To do this, follow the steps below.
📌 STEPS:
- First, go to the Data tab > Get Data > Launch Power Query Editor.
- Then, there will open a window named “Power Query Editor”.
- In the window, go to the Home tab, press on the New Source drop-down menu, and select Excel Workbook.
- Then, select a workbook and extract data from it.
- Alternatively, select the option Enter Data and input data manually.
- Here, make a column by pasting data from the workbook or inputting data manually. Then press OK.
- After that, go to the Add Column tab and press on the Custom Column option.
- Then, there will open a window named “Custom Column”.
- After that, give a suitable name for the column.
- Then, paste the formula given below:
=Text.PadStart([Number],4,"0")
- Then, press OK.
- Doing that, you will see a column named “Output” and the cells are of 4 digits with necessary zeros in front.
- Finally, press on Close & Load.
- Then, there will open a new sheet in the workbook with the data from the Power Query.
Things to Remember
- Using an Apostrophe at the front will convert the number to Text format and you have to 0 in front manually.
- Custom Formatting is the most useful option to add zero and make numbers of the same digits.
- Formatting numbers to Text will allow you to add 0 in front but it will hamper doing calculations.
- REPT function will add the specified amount of zeros no matter the number of digits. So, to make the number of the same digits, you have to use the REPT and IF functions together.
- TEXT function does the work of Custom Formatting.
- CONCAT function will also add the given amount of zeros no matter what the number of digits. So it will make the numbers of the same digit.
- RIGHT & BASE function will be useful to make the numbers of the same digit.
- And finally, Power Query will help to extract data and format them from external sources.
Download Practice Workbook
You can download the practice workbook from here:
Conclusion
In this article, I have tried to show you how to add 0 in front of numbers in Excel. Here, I have used 5 methods and some formulas to add 0 in front of numbers in Excel. I believe you have learned the methods from the article and will practice these yourself. I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.
Related Articles
<< Go Back to Pad Zeros in Excel | Number Format | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
