How to Add Prefix 0 in Excel (7 Easy Methods)

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.

dataset for prefix 0 in excel


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.

Text format prefix 0 in excel

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.

Custom Format prefix 0 in excel

  • 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.

Apostrophe prefix 0 in excel

  • 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)

CONCAT Function prefix 0 in excel

  • Then, press ENTER to get the output.

  • Finally, use Fill Handle to AutoFill up to C11.

CONCAT Function prefix 0 in excel


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.

TEXT Function prefix 0 in excel

  • 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)

RIGHT Function prefix 0 in excel

  • Then, press ENTER to get the output.

  • Finally, use Fill Handle to AutoFill up to C11.

RIGHT Function prefix 0 in excel


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.

BASE Function prefix 0 in excel

  • Finally, use Fill Handle to AutoFill up to C11.

BASE Function prefix 0 in excel


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.


Related Articles

Akib

Akib

Hi, this is MD Akib Bin Rashid. I completed my BSc in Engineering back in 2019. After that, I did an MBA. Then, I joined as an Excel and VBA Content Developer at SOFTEKO Digital. Being passionate about data analytics and finance, I will try to assist you with Excel

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo