This is the sample dataset.

### Method 1 – Applying the Text Format to Add Leading Zeros

Steps:

• Go to the Home tab and select Number.
• Choose General.

• Select Text.

• Enter the numbers with leading zeros.

An error is displayed: numbers are stored as text.

• Select C5:C13.

• Click the Error Warning.
• Select Ignore Error.

This is the output.

Note: To use these numbers in calculations, change the format to Number.

### Method 2 – Entering Leading Zeros with a Custom Format in Excel

Steps:

• Right-click and select Format Cells.

• Select Custom in Category.
• Enter a format.
• Click OK.

This is the output.

### Method 3 – Using the Apostrophe (`'`) to Add Leading Zeros

Steps:

• Enter an Apostrophe (`'`) before the number with leading zeros.

• Press Enter to see the result.

• Remove the Error Warning by following the steps described in Method 1.

Read More: How to Write 00 in Excel

The syntax of the TEXT function is,

`=TEXT(value,format_text)`

Steps:

• Enter the following formula.
`=TEXT(C5,"000000")`

• Press Enter to see the result.

C5 is the value and “000000” refers to format_text.
• Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

### Method 5 – Using the Ampersand (&) Operator in Excel

Steps:

• Enter the following formula.
`="000"&C5`

• Press Enter to see the result.

The Ampersand (&) operator adds “000” to the value in C5.
• Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

### Method 6 – Using the CONCATENATE Function

The syntax of the CONCATENATE function is:

`=CONCATENATE(text1,text2,...)`

Steps:

• Enter the following formula.
`=CONCATENATE("000",C5)`

• Press Enter to see the result.

“000” is selected as text1 and C5 as text2.
• Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

### Method 7 – Adding Leading Zeros with the RIGHT Function in Excel

The syntax of the RIGHT function is:

`=RIGHT(text,num_char)`

Steps:

• Enter the following formula.
`=RIGHT("00000"&C5,6)`

• Press Enter to see the result.

Formula Breakdown

• “00000”&C5:  joins the texts and returns a single text.
• RIGHT(“00000″&C5,6):  returns 6 characters from the right side of the text.
• Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

### Method 8 – Using the RIGHT and the CONCATENATE Functions in Excel

Steps:

• Enter the following formula.
`=RIGHT(CONCATENATE("00000",C5),6)`

• Press Enter to see the result.

Formula Breakdown

• CONCATENATE(“00000”,C5): joins the two texts.
• RIGHT(CONCATENATE(“00000”,C5),6): returns 6 characters from the right side of the text.
• Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

### Method 9 – Adding Leading Zeros with the Excel BASE Function

The syntax of the BASE function is:

`=BASE(number, radix, min_length)`

Steps:

• Enter the following formula.
`=BASE(C5,10,6)`

• Press Enter to see the result.

C5 is selected as number, 10 as the radix, and 6 as min_length. The formula converts the number in C5 to base 10 (decimal) with a minimum length of 6.
• Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

### Method 10 – Using the CONCATENATE, REPT, and LEN Functions to add Leading Zeros

The syntax of the REPT function is:

`=REPT(Character,Times_to_repeat) `

The syntax of the LEN function is:

`=LEN(text)`

Steps:

• Enter the following formula.
`=CONCATENATE(REPT(0,6-LEN(C5)),C5)`

• Press Enter to see the result.

Formula Breakdown

• LEN(C5): returns the length of the value in C5.
• 6-LEN(C5):  the length of C5 is subtracted from 6.
• REPT(0,6-LEN(C5)):  repeats 0 the number of times specified by the previous calculations.
• CONCATENATE(REPT(0,6-LEN(C5)),C5):  joins the zeros and the value in C5.
• Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

Steps:

• Go to the Developer tab.
• Select Visual Basic to open the Visual Basic Editor window. You can also click Alt + F11.

• Click Insert.
• Choose Module.

• Enter the following code in the module.
``````Function Add_Leading_Zeroes(ref_num As Range, str_length As Integer)
Dim k As Integer
Dim output As String
Dim string_length As Integer
string_length = Len(ref_num)
For k = 1 To str_length
If k <= string_length Then
output = output & Mid(ref_num, k, 1)
Else
output = "0" & output
End If
Next k
End Function``````

Code Breakdown

• ref_num, and str_length are the two arguments.
• It returns an output with a specified number of leading zeros (the total length of the numbers is str_length).
• Save the code and go back to your worksheet.
• Select D5.
• Enter the following formula:
`=Add_Leading_Zeroes(C5,6)`

• Press Enter to see the result.

C5 is selected as ref_num and 6 as str_length.

• Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

Practice here.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF