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.
Watch Video – Add Leading Zeros in Excel
How to Add Leading Zeros in Excel: 11 Suitable Methods
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.
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.
- 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.
- Finally, you will get your desired output.
Read More: How to Add Leading Zeros in Excel Text Format
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.
- Consequently, the Format Cells dialog box will appear.
- Select Custom from Category.
- Then, write the format you want.
- After that, select OK.
- Finally, you will see that the cells have got your selected format and you have got leading zeros before the numbers.
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.
- 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.
Read More: How to Write 00 in Excel
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")
- After that, press Enter and you will get leading zeros before numbers.
- Further, drag the Fill Handle down to copy the formula to the other cells.
- Finally, you can see that I have copied the formula to all the other cells and got my desired output.
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
- Next, press Enter to get the result.
- 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
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)
- Thirdly, press Enter and you will get the leading zeros.
- 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.
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-digit 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)
- 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.
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)
- 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)
- Afterward, press Enter.
- 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)
- 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.
- Next, in the VBA toolbar, click on Insert.
- Then click on Module.
- 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
🔎 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.
Practice Section
Here, I have provided a practice sheet for you to practice how to add leading zeros in Excel.
Download Practice Workbook
You can download the practice workbook from here.
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.
