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.

How to Write 00 in Excel: 11 Easy Ways

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.

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.

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.

Step 3:

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

Read More: How to Add Leading Zeros in Excel

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.

Step 2:

• Secondly, press Enter to see the desired result.

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.

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.

Step 2:

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

Step 3:

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

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.

Step 5:

• Fifthly, in cell C5 type the number 5.

Step 6:

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

Step 7:

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

Read More: How to Put 0 in Excel in Front of Numbers

4. Applying Text Format to cWrite 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.

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.

Step 3:

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

Step 4:

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

Step 5:

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

Read More: How to Add Leading Zeros in Excel Text Format

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.

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.

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.

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

Step 2:

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

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.

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.

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

Step 2:

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

Step 3:

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

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.

Step 2:

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

Step 3:

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

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.

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

Step 2:

• Then, press Enter to see the result.

Step 3:

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

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.

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

Step 2:

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

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.
  

Step 2:

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

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

Step 4:

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

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.

Step 6:

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

Step 7:

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

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.

Download Practice Workbook

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

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. Our team is always concerned about your preferences.

Related Articles

• How to Add Leading Zeros to Make 10 Digits in Excel
• How to Write 001 in Excel

<< Go Back to Pad Zeros in Excel | Number Format | Learn Excel