How to Add Leading Zeros in Excel

This is the sample dataset.

Method 1 – Applying the Text Format to Add Leading Zeros

Steps:

Select the cells to add leading zeros. Here, C5:C13.
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:

Select the cells to add leading zeros. Here, C5:C13.
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:

Select the cell to add leading zeros. Here, C5.
Enter an Apostrophe (') before the number with leading zeros.

Press Enter to see the result.

Follow the steps above to add leading zeros to the other numbers.

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

Read More: How to Write 00 in Excel

Method 4 – Adding Leading Zeros with the TEXT Function

The syntax of the TEXT function is,

=TEXT(value,format_text)

Steps:

Select the cell to add leading zeros. Here, D5.
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:

Select the cell to add leading zeros. Here, D5.
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:

Select the cell to add leading zeros. Here, D5.
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:

Select the cell to add leading zeros. Here, D5.
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:

Select the cell to add leading zeros. Here, D5.
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:

Select the cell to add leading zeros. Here, D5.
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:

Select the cell to add leading zeros. Here, D5.
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.

Method 11 – Using VBA to add Leading Zeros

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
    Add_Leading_Zeroes = output
End Function

Code Breakdown

The Add_Leading_Zeros function is created.
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).