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.

**Table of Contents**hide

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

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

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

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

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

**How to Put Parentheses for Negative Numbers in Excel****Format a Number in Thousands K and Millions M in Excel (4 Ways)****How to Change Number Format from Comma to Dot in Excel (5 Ways)****Custom Number Format: Millions with One Decimal in Excel (6 Ways)**

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

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

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

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

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

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

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

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

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

**How to Apply Accounting Number Format in Excel! (Easy way)****Excel Custom Number Format Multiple Conditions****How to Convert Number to Percentage in Excel (3 Quick Ways)****[Solved]: Leading Zero Not Showing in Excel (9 Possible Solutions)****How to Round Off Numbers in Excel (4 Easy Ways)****Keep Leading Zeros in Excel CSV (4 Easy Ways)**