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.

**Now they are in**

*Note:***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 the Excel

**Toolbar**.

**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.

**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.

- 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.

**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

### 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.

**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.

### 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**.

**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)`

- 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.

## Related Articles

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