Akib Bin Rashid
Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. Sometimes we need to put a 0 in the first place of a cell. We can do it in various ways. In this article, I will elucidate 7 easy methods on how to add prefix 0 in Excel.
Download Practice Workbook
Download this workbook and practice while going through the article.
7 Easy Ways to Add Prefix 0 in Excel
This is the dataset for today’s article. I have some numbers and I will put 0 in front of these numbers.
1. Format as Text to Add 0 Prefix
First of all, I will show how you can change the format of a cell to add a 0 in the first place of a cell. This is a very simple method.
Steps:
- First of all, select C5:C11.
- Then, go to the Home
- After that, select the drop-down for format.
- Then, select Text as the format.
- After that, add 0s as many as you want. Excel will consider it a text.
Read More: How to Add Prefix Without Formula in Excel (2 Easy Ways)
2. Using Custom Format
Now, I will demonstrate another method that also requires changing the format. However, this time I will use Custom Formatting.
Steps:
- First of all, select the entire dataset.
- Then, go to the Home
- After that, select the icon shown in the image.
- Then, the Format Cells box will appear.
- After that, select Custom.
- Then, set the type as 000.
- After that, click OK.
- Now, when you type the 2-digit numbers, Excel will add a 0 at the beginning of the cell to make it a 3-digit
Note:
If the number has less than 3 digits, Excel will add 0s to make the number a 3-digit one. However, if the number exceeds 3 digits, the number will remain as it is.
Read More: How to Remove Prefix in Excel (6 Methods)
3. Put Apostrophe Before 0
This is perhaps the easiest way to add 0 as a prefix in a cell. You can add as many 0s as you want using this simplest method.
Steps:
- Write down ‘012 in C5.
- Excel will consider 012 as text.
- Do the same for other cells too. You can put as many 0s as possible after an apostrophe.
Read More: How to Remove Prefix 91 in Excel (4 Easy Ways)
4. Use CONCAT Function
Now, I will use Excel functions to add 0 as a prefix. First of all, I will show the use of the CONCAT function.
Steps:
- Go to C5 and write down the following formula.
=CONCAT(0,B5)
- Then, press ENTER to get the output.
- Finally, use Fill Handle to AutoFill up to C11.
5. Apply TEXT Function
The next function I am going to use is the TEXT function.
Steps:
- Go to C5 and write down the following formula.
=TEXT(B5,"000")
- Then, press ENTER to get the output.
- Finally, use Fill Handle to AutoFill up to C11.
6. Use RIGHT Function
Here, I will show the application of the RIGHT function to put 0s in the first place of a cell.
Steps:
- Go to C5 and write down the following formula.
=RIGHT("0"&B5,3)
- Then, press ENTER to get the output.
- Finally, use Fill Handle to AutoFill up to C11.
7. Insert BASE Function
Another function can be used to 0s in the first place of a cell. This is the BASE function.
Steps:
- Go to C5 and write down the following formula.
=BASE(B5,10,3)
- Then, press ENTER to get the output.
- Finally, use Fill Handle to AutoFill up to C11.
Things to Remember
- In General Format, Excel omits 0 if added in the first place of a cell.
Conclusion
In this article, I have explained 7 easy ways to add prefix 0 in Excel. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below. Please visit Exceldemy for more valuable articles like this.
