How to Write 00 in Excel (11 Easy Ways)

While working with various numbers in Excel, sometimes it is necessary to add a 0 or 00 at the beginning of the numbers. But by default, Excel will not keep the leading 0 or 00 after inputting the numbers with such conditions. So, this characteristic of Excel can cause problems while working. In this article, we will show you how to write 00 in Excel.


Download Practice Workbook

You can download the free Excel workbook here and practice on your own.


11 Easy Ways to Write 00 in Excel

Sometimes, users have to input phone numbers, zip codes, employee identity numbers, etc., into Excel. Naturally, these numbers can have 00 in front of them. As a result, users cannot automatically input these numbers with 00 in Excel. So, in this article, we will show you eleven different methods to write 00 in Excel. For our working purposes, we will use the following data set.

Easy Ways to Write 00 in Excel

To write 00 in Excel, we will use symbols like an apostrophe and an ampersand. We will also change the cell format to text and add a custom cell format. Again, we will insert some Excel functions like the CONCAT function, the RIGHT function, the TEXT function, the TEXTJOIN function, the BASE function, and the REPT function. Finally, we will apply a VBA code to write 00 in Excel.

1. Adding Apostrophe to Write 00 in Excel

In our first method, we will add an apostrophe (‘) before our number to write 00. Let’s see the following steps for a better understanding.

Step 1:

  • First of all, in our Output column, type an apostrophe (‘).
  • Then, type 00 and, after that type the required number.

Easy Ways to Write 00 in Excel

Step 2:

  • Secondly, press Enter after typing.
  • Here, you will see the leading 00 before the number.
  • This process transforms the number into text so the 00 won’t disappear after entering.

Easy Ways to Write 00 in Excel

Step 3:

  • Finally, repeat the above steps for the following cells to write 00.

Easy Ways to Write 00 in Excel

Read More: How to Add Leading Zeros in Excel (4 Quick Methods)


2. Including Ampersand (&) Operator to Write 00 in Excel

You can also write 00 before a number using the ampersand (&) operator. See the below-given steps for this procedure.

Step 1:

  • First of all, type the following formula in cell B5.
="00"&B5

Easy Ways to Write 00 in Excel

Step 2:

  • Secondly, press Enter to see the desired result.

Easy Ways to Write 00 in Excel

Step 3:

  • Thirdly, use the AutoFill feature to drag the formula to the lower cells of the data set.
  • Finally, you will see 00 before all the numbers.

Easy Ways to Write 00 in Excel


3. Using Custom Number Format to Write 00 in Excel

You can use a custom number format for your data set to write 00 before them. Below are the detailed steps of this procedure.

Step 1:

  • Firstly, select a cell where you want to see the desired output.

Easy Ways to Write 00 in Excel

Step 2:

  • Secondly, right-click on the cell and select the Format Cells… command.

Easy Ways to Write 00 in Excel

Step 3:

  • Thirdly, you will see the Format Cells dialogue box.
  • Then, go to the Custom tab.

Easy Ways to Write 00 in Excel

Step 4:

  • Fourthly, in the Type box type 00.
  • Then, type a number of # which is equal to the digits of your number.
  • For instance, 5 is a one-digit number.
  • So, we will type a single #.
  • Lastly, press OK.

Easy Ways to Write 00 in Excel

Step 5:

  • Fifthly, in cell C5 type the number 5.

Easy Ways to Write 00 in Excel

Step 6:

  • Then, after pressing Enter, you will see the 00 before the number.

Easy Ways to Write 00 in Excel

Step 7:

  • Finally, customize the format of the other cells according to the digits of those numbers and get the desired result.

Easy Ways to Write 00 in Excel

Read More: How to Put 0 in Excel in Front of Numbers (5 Handy Methods)


4. Applying Text Format to Write 00 in Excel

You can also write 00 before numbers by applying the text format. To do so, see the following steps.

Step 1:

  • Firstly, select the cell range C5:C10.
  • Then, go to the Numbers group from the Home tab of the ribbon.
  • From the tab, select the small arrow which is at the lower right side of the group.

Easy Ways to Write 00 in Excel

Step 2:

  • Secondly, you will see the Format Cells dialogue box like in the previous method.
  • Then, from the Number tab choose the Text tab.
  • Lastly, press OK.

Easy Ways to Write 00 in Excel

Step 3:

  • Thirdly, type the number 5 with 00 in the leading position.

Easy Ways to Write 00 in Excel

Step 4:

  • Finally, press Enter and you will see the number will convert into text format and the leading 00 won’t disappear.

Easy Ways to Write 00 in Excel

Step 5:

  • Consequently, type the other numbers in the following cells in the same way.

Easy Ways to Write 00 in Excel

Read More: How to Add Leading Zeros in Excel Text Format (10 Ways)


5. Using CONCAT Function to Write 00 in Excel

By using the CONCAT function of Excel, you can write 00 before any number. The steps for this procedure are as follows.

Step 1:

  • First of all, type the following CONCAT function formula in cell B5.
=CONCAT("00",B5)

Easy Ways to Write 00 in Excel

Step 2:

  • Secondly, press Enter and you will get the desired number with 00 in it.
  • Finally, use AutoFill to apply the formula in the lower cells of the data set.

Easy Ways to Write 00 in Excel

Read More: How to Add Leading Zeros in Excel by CONCATENATE Operation


6. Utilizing RIGHT Function

The RIGHT function in Excel also helps you to write 00 before any numbers. We have discussed the detailed steps below for this procedure.

Step 1:

  • Firstly, type the following formula of the RIGHT function in cell C5.
=RIGHT("00"&B5,3)
  • In the character syntax of the formula, provide the number of digits after adding 00 to it.
  • For example, we will use the number 5 in our first case.
  • After adding 00, it will become a three digits number.
  • So, in the syntax, we will input 3 as the number of characters.

Easy Ways to Write 00 in Excel

Step 2:

  • Secondly, press Enter and you will see the number with 00 in it.

Easy Ways to Write 00 in Excel

Step 3:

  • Thirdly, follow the above steps and modify the formula for the number of digits of the other numbers.
  • Finally, you will get the desired results by successfully adding 00.

Easy Ways to Write 00 in Excel


Similar Readings


7. Inserting TEXT Function to Write 00 in Excel

Another method to write 00 in Excel is to insert the TEXT function. Let’s go through the following steps for a better understanding.

Step 1:

  • First of all, type the following formula of the TEXT function in cell C5.
=TEXT(B5,"000")
  • Here, in the format_text syntax, insert the number of digits the required number will become after adding 00.
  • For example, we want to write 5 as 005.
  • So, we will insert three zeroes.

Easy Ways to Write 00 in Excel

Step 2:

  • Secondly, press Enter to get the expected result in cell C5.

Easy Ways to Write 00 in Excel

Step 3:

  • Finally, fill up the other cells in the same way.

Easy Ways to Write 00 in Excel


8. Using TEXTJOIN Function to Write 00 in Excel

Apart from inserting the TEXT function, you can also use the TEXTJOIN function to write 00 in Excel. How you can do it is given in the following steps.

Step 1:

  • Firstly, in cell C5, type the following formula of the TEXTJOIN function.
=TEXTJOIN("",,"00",B5)

Easy Ways to Write 00 in Excel

Step 2:

  • Secondly, press Enter to get the desired number with 00 in it.

Easy Ways to Write 00 in Excel

Step 3:

  • Finally, use the AutoFill to apply the formula to the lower cells of that column.

Easy Ways to Write 00 in Excel


9. Applying BASE Function

Another function that you can apply to write 00 in Excel is the BASE function. We are providing more details about this procedure in the following steps.

Step 1:

  • First of all, type the following formula of the BASE function in cell C5.
=BASE(B5,10,3)
  • In the min_length syntax of the formula, give the length of the string after transforming.
  • For our first example, it is a three-digit number.

Sample Data Set

Step 2:

  • Then, press Enter to see the result.

Sample Data Set

Step 3:

  • Finally, repeat the above steps with the correct length of string for each input.

Sample Data Set


10. Inserting REPT Function to Write 00 in Excel

The last function we will insert to accomplish our task is the REPT function. See the following steps for a better understanding.

Step 1:

  • In the beginning, type the following REPT function formula in cell C5.
=REPT("00",1)&B5
  • After adding 00 to the formula, set the number of repetitions of 00.
  • As we want to have 00 only once in our output we will set the number of repeat times to 1.

Sample Data Set

Step 2:

  • Secondly, hit the Enter button and get the desired result.
  • Finally, drag the AutoFill for the final result.

Sample Data Set


11. Applying VBA to Write 00 in Excel

Finally, for our last method, we will apply a VBA code to write 00 in Excel. For a better understanding, see the following steps.

Step 1:

  • Firstly, go to the Developer tab of the ribbon.
  • Then, from there choose the Visual Basic command from the Code group.

Sample Data Set

Step 2:

  • Secondly, you will see the VBA window after choosing that command.
  • Then, from the Insert tab select Module.

Sample Data Set

Step 3:

  • Thirdly, copy the following VBA code into the module.
Sub Write_00_in_Excel()
'Addressing variables
Dim ws As Worksheet
'Giving specific name of the worksheet
Set ws = Worksheets("VBA")
'Writing 00 before a number and we are setting the number format as per the number of digits in that particular number
'Setting the output cell range and the number format
ws.Range("C5").NumberFormat = "000"
'Addressing from cell range from which the number will be changed
ws.Range("C5") = ws.Range("B5")
End Sub

Sample Data Set

                                                               VBA Code Breakdown
  • The function name is Write_00_in_Excel.
  • Dim ws As Worksheet: We have addressed a worksheet variable.
  • Set ws = Worksheets(“VBA”): Giving the specific name of the current worksheet.
  • ws.Range(“C5”).NumberFormat = “000”: Setting the output cell location and the number format after adding 00.
  • ws.Range(“C10”) = ws.Range(“B10”): Addressing the cell location from which the code will take input.

Step 4:

  • Fourthly, save the VBA code and press the play button or F5 to run the code.

Sample Data Set

Step 5:

  • In the fifth step, after playing the code, you will get the following result with 00 before the number 5 in cell C5.

Sample Data Set

Step 6:

  • In the sixth step, we will modify the code for the number 45095 in cell C9 and play it.

Sample Data Set

Step 7:

  • Then, you will get the desired number with 00 before it after running the code.

Sample Data Set

Step 8:

  • Finally, modify the code for each number with its cell location and digit length.
  • Consequently, you will see all the numbers with 00 in the leading position.

Sample Data Set

Read More: How to Remove Leading Zeros in Excel (7 Easy Ways + VBA)


Conclusion

That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to write 00 in Excel by using any of the above-described methods. Please share any further queries or recommendations with us in the comments section below. The Exceldemy team is always concerned about your preferences.


Related Articles

Araf

Araf

I am Araf. I have completed my B.Sc in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Currently I am working as an Excel & VBA Content Developer in Softeko. With proper guideline and aid of Softeko I want to be a flexible data analyst. With my acquired knowledge and hard work, I want to contribute to the overall growth of this organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo