How to Add Leading Zeros in Excel (11 Suitable Methods)

While working in Excel, we often have to add leading zeros before one or more numbers in Excel. This is in fact quite easy to implement. Today I will be showing all possible ways how you can achieve that.


Download Practice Workbook

You can download the practice workbook from here.


11 Suitable Methods to Add Leading Zeros in Excel

Let us have a look at this dataset. We have the Employee Record of the company here. There are 2 columns named Employee Name and ID. You can see that the ID does not contain leading zeros. But, I want to add leading zeros in front of them to make them six digits numbers. Now, I will show you how to add leading zeros in Excel in 11 different methods.

Dataset for How to Add Leading Zeros in Excel


1. Apply Text Format to Add Leading Zeros

You can use Excel Toolbar options to insert leading zeros before any number. Here, I will use the Text format from the Toolbar to add leading zeros in Excel. Let’s see the steps.

Steps:

  • Firstly, select the range of cells where you want to insert leading zeros. Here I select cells C5 to C13.
  • Secondly, go to Home > General option in Excel Toolbar under Number groups.
  • Thirdly, Click on the drop-down menu.

Apply Text Format to Add Leading Zeros

  • Next, a drop-down menu will appear.
  • Scroll down and click on the last option, Text.

  • Next, insert the numbers with leading zeros. Here, you will see an error showing because the numbers are stored as text.

  • To remove the errors, select the cells where the error is showing. Here, I selected the cell range C5:C13.

  • After that, Click on the Error Warning.
  • Then, select Ignore Error.

Removing Error Warning to Add Leading zeros in Excel

  • Finally, you will get your desired output.

Note: Now they are in Text Format, so you can not use them in any calculation. If you want to use them in any calculation, change their format to Number from Excel Toolbar.

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


2. Insert Leading Zeros with Custom Format in Excel

Now, I will use the Custom format to add leading zeros in Excel. By using this method you will only change how the numbers are displayed. Numbers will still be stored as numbers and you will be able to use them in calculations. Let me show you how you can do it.

Steps:

  • First, select the cells where you want to add leading zeros. Here, I selected the cell range C5:C13.
  • Next, Right-click on the selected cells.
  • Then, select Format Cells.

Add Leading Zeros with Custom Format in Excel

  • Consequently, the Format Cells dialog box will appear.
  • Select Custom from Category.
  • Then, write the format you want.
  • After that, select OK.

Format Cells Dialog Box to add Leading Zeros in Excel

  • Finally, you will see that the cells have got your selected format and you have got leading zeros before the numbers.

Read More: Add or Keep Leading Zeros in Excel (10 Suitable Ways)


3. Employ Apostrophe (') to Add Leading Zeros

You can use Apostrophe (') in front of a number to convert it to Text Format. Then insert the required number of leading zeros in the front. Let’s see how it is done.

Steps:

  • Firstly, select the cell where you want to insert the numbers with leading zeros. Here, I selected cell C5.
  • Secondly, insert an Apostrophe (') before the number with leading zeros.

Employ Apostrophe (') to Add Leading Zeros in Excel

  • After that, press Enter and you will get your desired numbers with leading zeros.

  • Then, enter all the numbers in the same way.

  • Finally, remove the Error Warning by following the steps from Method-1.

Related Content: Excel Number Stored As Text [4 Fixes]


Similar Readings


4. Insert Leading Zeros Using TEXT Function

Now, I will use the TEXT function to add leading zeros in Excel. The syntax of the TEXT function is,

=TEXT(value,format_text)

Here, value is the number that you want to convert to text, and format_text is the format in which you want to have your text. Now, the function returns the value in your selected format.

Let’s see the steps of using the TEXT function to add leading zeros in Excel.

Steps:

  • In the beginning, select the cell where you want to add the leading zeros before the number. Here, I selected cell D5.
  • Then, in cell D5 write the following formula.
=TEXT(C5,"000000")

Add Leading Zeros Using TEXT Function in Excel

  • After that, press Enter and you will get leading zeros before numbers.

Here, in the TEXT function, I selected cell C5 as the value and “000000” as format_text. Now, the formula returns your selected number in the specified format.
  • Further, drag the Fill Handle down to copy the formula to the other cells.

Dragging Fill Handle to Copy Formula to Add Leading Zeros in Excel

  • Finally, you can see that I have copied the formula to all the other cells and got my desired output.

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


5. Apply Ampersand (&) Operator in Excel

You can also use the Ampersand (&) operator to add leading zeros in Excel. The Ampersand (&) operator is generally used to join texts, formulas, or text with formulas. Let’s see the steps.

Steps:

  • To begin with, select the cell where you want to add leading zeros.
  • Then, write the following formula in that selected cell.
="000"&C5

Apply Ampersand (&) Operatore in Excel to Add Leading Zeros

  • Next, press Enter to get the result.

Here, the Ampersand (&) operator joins “000” with the value in cell C5. By using this operator you will be able to add specific numbers of zeros.
  • Afterward, drag the Fill Handle to copy the formula.

  • In the end, you can see that I have copied the formula to all the other cells and got my desired output.

Read More: How to Add Leading Zeros to Make 10 Digits in Excel (10 Ways)


6. Employ CONCATENATE Function to Place Leading Zeros

The CONCATENATE function is also used to add leading zeros in Excel. Syntax of the CONCATENATE function is,

=CONCATENATE(text1,text2,...)

It takes two or more texts as input. And then, joins these texts and returns one single text. Let’s see the steps.

Steps:

  • Firstly, select the cell where you want to add the leading zeros. Here, I selected cell D5.
  • Secondly, in cell D5 write the following formula.
=CONCATENATE("000",C5)

Add CONCATENATE Function to Place Leading Zeros in Excel

  • Thirdly, press Enter and you will get the leading zeros.

Here, in the CONCATENATE function, I selected “000” as text1, and cell C5 as text2. Now, the formula will join the two texts and return one single text.
  • After that, drag the Fill Handle to get the result.

  • Finally, you can see that I have copied the formula to all the other cells.

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


7. Add Leading Zeros Utilizing RIGHT Function in Excel

In this method, I will use the RIGHT function to add leading zeros in Excel. By using this function you will be able to add zeroes according to your ID. Suppose, your ID contains different numbers of digits for different employees and you want to make them 6 digits numbers by adding leading zeros. In this type of situation, you can use the RIGHT function.

The syntax for the RIGHT function is,

=RIGHT(text,num_char)

Here, the text is the string from which you want to extract the characters and num_char is the number of characters you want to extract. The function returns the string with the specified number of characters from the right as output.

Let’s see the steps.

Steps:

  • In the beginning, select the cells where you want to add the leading zeros.
  • Next, write the following formula in that selected cell.
=RIGHT("00000"&C5,6)

Add Leading Zeros Utilizing RIGHT Function in Excel

  • Then, press Enter to get the result.

🔎 How Does the Formula Work?

  • “00000”&C5: Here, the Ampersand (&) operator joins the texts and returns them as a single text.
  • RIGHT(“00000″&C5,6): Now, the RIGHT function returns 6 characters from the right side of the text.
  • Afterward, drag the Fill Handle to copy the formula to the other cells.

  • Consequently, you will see that I have copied the formulas to all the other cells.

Read More: How to Concatenate Numbers with Leading Zeros in Excel (6 Methods)


8. Use RIGHT and CONCATENATE Functions in Excel

Now, I will show you how you can add leading zeros by using both the RIGHT function and the CONCATENATE function in Excel. You can use this formula where you don’t know how many leading zeros you will need to get a specific number of digits. Let’s see the steps.

Steps:

  • Firstly, select the cell where you want to add leading zeros. Here, I selected cell D5.
  • Secondly, in cell D5 write the following formula.
=RIGHT(CONCATENATE("00000",C5),6)

Use RIGHT and CONCATENATE Functions in Excel to Add Leading Zeros

  • Thirdly, press Enter to add the leading zeros.

🔎 How Does the Formula Work?

  • CONCATENATE(“00000”,C5): Here, the CONCATENATE function will join these two texts.
  • RIGHT(CONCATENATE(“00000”,C5),6): Now, the RIGHT function returns 6 characters from the right side of the text.
  • Next, drag the Fill Handle down to copy the formula.

  • Lastly, you will see that you have copied the formula and got your desired output.


9. Add Leading Zeros Using Excel BASE Function

In this method, I will show you how to add leading zeros using the BASE function in Excel. The syntax of the BASE function is,

=BASE(number, radix, min_length)

Here, the number refers to the number you want to convert, radix refers to the base that you want to convert the number into, and min_length refers to the minimum length you want. It returns the number in text format with the specified number of digits.

Let’s see how you can use this function to add leading zeros.

Steps:

  • First, select the cell where you want the numbers with leading zeros.
  • Then, write the following formula in that selected cell.
=BASE(C5,10,6)

Add Leading Zeros Using Excel BASE Function 

  • Afterward, press Enter.

Here, in the BASE function, I selected cell C5 as the number, 10 as the radix, and 6 as min_length. Now, the formula converts the number in cell C5 to base 10 (decimal) with a minimum length of 6.
  • Next, drag the Fill Handle down to copy the formula.

  • Finally, you can see that I have copied the formula to the other cells.


10. Employ CONCATENATE, REPT, and LEN Functions to Put Leading Zeros

Now, I will show you how you can use the CONCATENATE, REPT, and LEN functions to add leading zeros in Excel.

Here, The syntax for the REPT function is,

=REPT(Character,Times_to_repeat) 

This function repeats a character a specified number of times.

And, the syntax for the LEN function

=LEN(text)

The LEN function returns the length of any text.

Let’s see the steps of adding leading zeros using these functions.

Steps:

  • In the beginning, select the cell where you want to add leading zeros.
  • Then, write the following formula in that selected cell.
=CONCATENATE(REPT(0,6-LEN(C5)),C5)

Employ CONCATENATE, REPT, and LEN Functions to Add Leading Zeros in Excel

  • Next, press Enter.

🔎 How Does the Formula Work?

  • LEN(C5): Here, the LEN function returns the length of the value in cell C5.
  • 6-LEN(C5): Now, the length of C5 is subtracted from 6.
  • REPT(0,6-LEN(C5)): Here, the REPT function will repeat 0 to the specified number of times that it got from the previous calculations.
  • CONCATENATE(REPT(0,6-LEN(C5)),C5): Finally, the CONCATENATE function joins the zeros with the value in cell C5.
  • After that, drag the Fill Handle down to copy the formula.

  • Next, you will see that you have copied the formula to all the other cells and got your desired output.


11. Use VBA to Include Leading Zeros in Excel

You can also use this VBA Code to add leading zeros in Excel. Let’s see the steps.

Steps:

  • Firstly, go to the Developer tab.
  • Secondly, select Visual Basic to open the Visual Basic Editor window.
  • Or, Click Alt + F11 on your keyboard. It will open the VBA (Visual Basics for Application) window in your Excel file.

Use VBA to add Leading Zeros in Excel

  • Next, in the VBA toolbar, Click on Insert.
  • Then Click on Module.

Opening Modulr to Add Leading Zeros in Excel

  • Consequently, a module will open.
  • Then, write the following code in that 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

VBA Code to Create a User Defined Function to add a Leading Zeros in Excel

🔎 How Does the Code Work?

  • Here, it creates a function called Add_Leading_Zeros
  • Now, the function takes two arguments, a range of cells named ref_num, and an integer named str_length.
  • It returns an output with a specified number of leading zeros to the numbers (the total length of the numbers will be str_length) given as input.
  • Afterward, Save the code and go back to your worksheet.
  • Then, select the cell where you want to add leading zeros.
  • Next, write the following formula in that selected cell.
=Add_Leading_Zeroes(C5,6)

  • After that, press Enter to get the result.

Here, in the Add_Leading_Zeroes function, I selected cell C5 as ref_num and 6 as str_length. Now, the formula will return the number in cell C5 as a 6 digit number by adding leading zeros.

  • After that, drag the Fill Handle down to copy the formula.

  • In the end, you can see that I have copied the formula to the other cells and got my desired results.

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


Practice Section

Here, I have provided a practice sheet for you to practice how to add leading zeros in Excel.

Practice sheet for How to Add Leading Zeros in Excel


Conclusion

These are the ways you can use to add a specified number of leading zeros before numbers in Excel. Do you know any other method? Let us know in the comment section.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo