How to Write 00 in Excel (11 Easy Ways)

Users can’t automatically input numbers with 00 at the front in Excel, but there are ways to go around that restriction. We will consider the following data set.

Method 1 – Adding an Apostrophe to Write 00 in Excel

Steps:

In the output column, type an apostrophe (‘).
Type 00 and then type the required number.

Press Enter.
You will see the leading 00 before the number. This process transforms the number into text so the 00 won’t disappear after entering.

Repeat for the other cells.

Read More: How to Add Leading Zeros in Excel

Method 2 – Including an Ampersand (&) Operator to Write 00 in Excel

Steps:

Use the following formula in cell B5.

="00"&B5

Press Enter to see the desired result.

Use the AutoFill feature to copy the formula to the other result cells of the data set.

Method 3 – Using a Custom Number Format to Write 00 in Excel

Steps:

Select a cell where you want to see the desired output.

Right-click on the cell and select the Format Cells… command.

You will see the Format Cells dialog box.

Go to the Custom tab.

In the Type box, type 00.
Type a number of # equal to the digits of your number.
For instance, 5 is a one-digit number, so we typed a single #.
Press OK.

In cell C5, type the number 5 and press Enter.

You will see the 00 before the number.

Customize the format of the other cells according to the number of digits of those numbers.

Method 4 – Applying the Text Format to Write 00 in Excel

Steps:

Select the cell range C5:C10.
Go to the Numbers group from the Home tab of the ribbon.
Select the small arrow which is at the bottom-right corner of the group.

You will get the Format Cells dialog box.

From the Number tab, choose the Text category.
Press OK.

Type the number you need.

Press Enter and the number will convert into text format and the leading 00 won’t disappear.

Repeat for the other numbers in the following cells.

Method 5 – Using the CONCAT Function to Write 00 in Excel

Steps:

Insert the following CONCAT function formula in cell B5.

=CONCAT("00",B5)

Press Enter and you will get the desired number with 00 in it.
Use AutoFill to apply the formula in the other cells of the data set.

Method 6 – Utilizing the RIGHT Function

Steps:

Insert the following formula in cell C5.

=RIGHT("00"&B5,3)

In the character syntax of the formula, provide the number of digits after adding 00 to it. 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.

Press Enter and you will see the number with 00 in it.

Repeat and modify the formula for the number of digits of the other numbers.

Method 7 – Inserting the TEXT Function to Write 00 in Excel

Steps:

Insert the following formula in cell C5.

=TEXT(B5,"000")

In the format_text argument, insert the number of digits the required number will become after adding 00. We want to write 5 as 005, so we will insert three zeroes.

Press Enter to get the expected result in cell C5.

Fill up the other cells in the same way.

Method 8 – Using the TEXTJOIN Function to Write 00 in Excel

Step:

In cell C5, insert the following formula:

=TEXTJOIN("",,"00",B5)

Press Enter to get the desired number with 00 in it.

Use AutoFill to apply the formula to the other cells of that column.

Method 9 – Applying the BASE Function

Steps:

Use the following formula in cell C5.

=BASE(B5,10,3)

In the min_length argument of the formula, give the length of the string after transforming. For our first example, it is a three-digit number.

Press Enter to see the result.

Repeat the above steps with the correct length of string for each input.

Method 10 – Inserting the REPT Function to Write 00 in Excel

Steps:

Use the following formula in cell C5.

=REPT("00",1)&B5

Hit the Enter button and get the desired result.
Drag the fill handle down to AutoFill for the final result.

Method 11 – Applying VBA to Write 00 in Excel

Steps:

Go to the Developer tab of the ribbon.
Choose the Visual Basic command from the Code group.

You will see the VBA window.
From the Insert tab, select Module.

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

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.