In Microsoft Excel, leading zeros can be extremely useful in a variety of applications. A leading zero is any “0” digit in a number string that comes before the first nonzero digit. Sometimes we may have come across a circumstance when we needed to add a leading zero in Excel. In this article, we will demonstrate how to add leading zeros in Excel text format.

**Table of Contents**hide

## What Are Leading Zeros in Excel?

Leading zeros are the zeros that are used to fill up empty spaces. Usually, zeros are not allowed to show in front of the number in Excel. That time, you can convert the number into text and you will be allowed to add zeros. Leading zeros are useful in different fields like you want to represent a phone number or an Id number of fixed digits. Like, there are **20 **students in a class, and **ID **starts from **1** to **20**. So, for **1** to **9** **ID**s will be single digits, and the rest are double digits. To show all the **ID**s of the same digits we add a leading zero for **1** to **9** and all **ID**s are on the same digits now.

**How to Add Leading Zeros in Excel Text Format: 11 Methods**

There is a wide range of options for how to add leading zeros in Excel text format. We will discuss **11** methods in the section below.

Here, we will use the following dataset that consists of some employees’ names and **ID**s. We will add leading zeros with **ID**s that are in **Text** format.

**1. Add Leading Zeros Manually in Excel**

In this section, we will **put zeros before the numbers** in text format manually. After that, we will use the **Flash Fill** feature. Look at the below section for details. Here, we will add **two** (**2**) leading zeros.

**📌 ****Steps:**

- First, move to
**cell D5**and manually insert the value of**cell C5**with two zeros (**00**).

- Now, select
**range D5:D11 >>**go to**Data**tab**>>**select**Flash Fill**from**Data Tools**group.

Now, all the corresponding values of **range C5:C10** have been copied to **range D5:D11 **with two leading zeros. We used the **Flash Fill** so that we do not need to perform the previous task repeatedly.

You will also see error notifications with the values. We need to remove the error notification.

- Click on the drop-down of the error symbol and select the
**Ignore Error**option from the list.

- You can see no error is showing now.

**2. Use of Apostrophe Operator to Add Leading Zeros in Excel**

In this section, we will use the apostrophe operator to add leading zeros in Excel. This is also applicable for cells that are not in text format. Look at the section below.

**📌 ****Steps:**

- Go to
**cell D5**and insert the data of**cell C5**with an apostrophe operator and two zeros (**00**) at the leading position.

- Select
**range D5:D10**and apply the**Flash Fill**by pressing**Ctrl+E**. This will follow the format of**cell D5**to the selected cells.

You can see error notifications with all cells. You can remove all those errors as shown in the previous section.

**Read More:** **Excel VBA: Format Cell as Text**

**3. Adding Leading Zeros in Excel Text Format with Ampersand**

In this section, we will use a formula to add leading zeros in **Excel** text format using the **ampersand** (**&**) operator.

- Go to
**cell D5**and insert the following formula.

`="00"&C5`

- Then, pull the
**Fill Handle**icon.

We can see two leading zeros have been added.

**4. TEXTJOIN Function Add Leading Zeros in Excel Text Format**

Here, we will use **the TEXTJOIN function**. This function is used to join multiple texts or numbers. We also have the opportunity to use a delimiter using this function. Here, this function will add leading zeros to the existing data in **Excel**.

- Insert the following formula on
**cell D5**based on the**TEXTJOIN**function and then pull the**Fill Handle**icon.

`=TEXTJOIN( ,TRUE,"00",C5)`

We can see leading zeros have been added without any hassle.

**5. Excel CONCAT Function Adds Leading Zeros**

Usually, the** CONCAT** function is used to combine multiple texts in Excel. This function is available in the latest versions of Excel. Here, we are using the **CONCAT **function to add leading zeros with text format.

- Input the following formula on
**cell D5**and drag the**Fill Handle**icon downwards.

`=CONCAT("00",C5)`

**Note:**

This **CONCAT** function is not available in the older Excel versions. You will get **the CONCATENATE function** in the older versions, and the formula will look like this.

`=CONCATENATE("00",C5)`

**6. How to Add Leading Zeros in Excel Text Format: TEXT Function**

In Excel, **the TEXT function** is used to transform integers into text. It essentially converts a numeric number to a text string.

In this method, we used the **TEXT function** to **add leading zeros** in Excel.

- Now, insert the following formula on
**cell D5**.

`=TEXT(C5,"00000")`

- After that, drag the
**Fill Handle**icon to**cell D11**.

**7. Excel RIGHT Function to Add Leading Zeros**

**The RIGHT function** returns a specific number of characters from the right side or the end of a given reference. Here, we will show how the **RIGHT **function is used to add leading zeros in Excel.

- Insert the formula based on the
**RIGHT**function and ampersand operator on**cell D5**.

`=RIGHT("0000"&C5,5)`

- Then, pull the
**Fill Handle**icon.

**Formula Explanation**

**“0000”&C5**

Here, the ampersand operator joins “**0000**” and the value of **cell C5**.

**Result: 0000112**

**RIGHT(“0000″&C5,5)**

The **RIGHT **function extracts **5** letters or characters from the right side or the end.

**Result: 00112**

**Similar Readings**

**8. Combination of REPT & LEN Functions to Add Leading Zeros**

**The REPT function** is used to repeat a text or number several times in the result. And **the LEN function** is used to determine the number of characters in a given reference. In this section, we will use a combination of the **REPT**, **LEN **functions, and ampersand operators to add leading zeros. Here, our final result will be **5** digits for each cell.

- Insert the following formula on
**cell D5**and then pull the**Fill Handle**icon.

`=REPT(0,5-LEN(C5))&C5`

**Formula Explanation**

**LEN(C5)**

This determines the length of **cell C5**.

**Result: 3**

**5-LEN(C5)**

Subtract the length of **C5 **from **5** to get a result with **5** digits.

**Result: 2**

**REPT(0,5-LEN(C5))**

Repeat **zeros (0)** based on the result obtained from the subtraction.

**Result: 00**

**REPT(0,5-LEN(C5))&C5**

The ampersand adds zeros with the value of **C5**.

**Result: 00121**

**9. Excel BASE Function Adds Leading Zeros**

**The BASE function** in Excel translates a number to its text with a specific base. With the **BASE function**, we can add leading zeros. Here, we are considering **10** base numbers.

- Insert the following formula on
**cell D5**and pull the**Fill Handle**icon.

`=BASE(C5,10,5)`

Here, the **BASE **function converts **C5 **into a **10-base** number with **5** digits.

**10. Excel Power Query to Add Leading Zeros**

**Power Query** is an **Excel **feature that is ideal for users who require data preparation but do not require a full array of analytic capabilities.

The **Power Query Editor** is the main data preparation interface, where you may connect to a variety of data sources and preview data while applying hundreds of different data transformations. We can add leading zeros with the help of the power query.

**📌 ****Steps:**

- Select
**range B4:C10 >> Data**tab**>>**click on**From Table/Range**from the**Get & Transform Data**group.

- The
**Create Table**window appears. Mark**My table has headers**option and click**OK**.

The selected range is also showing here.

- We can see the
**Power Query**window and**ID**column are presenting data in number form.

- First, convert data into text format as we need to work with text-formatted data.
- Select the heading of the
**ID**column and press the right button of the mouse. - Choose the
**Text**option from the**Context Menu**.

- A pop-up warning is showing to accept the change of column type. Choose to
**Replace current**option.

- We can see data of the
**ID**column are left aligned which means they are in text format. - Now, go to
**Add Column >> Custom Column**to add a new column, where we will present data with leading zeros.

**Custom Column**window appears.- Set a name in the
**New column name**section. - Insert the following formula in the
**Custom column formula**section and finally press**OK**.

`=Text.PadStart([ID],5,"0")`

- We can see a new column data with leading zeros.

**11. Excel VBA to Add Leading Zeros**

When we need to apply any specific task multiple times, we use the **VBA** macro. Also, VBA code is helpful to work with large datasets. In this section, we will form a VBA code that will add leading zeros to the existing data.

**📌 ****Steps:**

- First, go to the
**Leaf Bar**and press the right button of the mouse. - Choose the
**View Code**option from the**Context Menu**.

- We entered the
**VBA**application window. - Go to
**Insert >> Module >>**paste the following**VBA**code on the module.

```
Sub Add_Leading_Zeros()
Dim Cell_1 As Range
Dim Total_Digits, Required_Zeros As Long
Total_Digits = 5
Set Cell = Selection
For Each Cell_1 In Selection
Required_Zeros = Total_Digits - Len(Cell_1.Value)
If Required_Zeros > 0 Then
Cell_1.Offset(0, 1).Value = String(Required_Zeros, "0") & Cell_1.Value
Else
Cell_1.Offset(0, 1).Value = Cell_1.Value
End If
Next Cell_1
End Sub
```

**Code Breakdown**

**Dim Cell_1 As Range, Dim Total_Digits, Required_Zeros As Long**

Declaring the variables with type.

**Total_Digits = 5**

Set the value of **Total_Digits **5.

**Set Cell = Selection**

Select a range in the worksheet as the input of **Cell**.

**For Each Cell_1 In Selection**

Apply the loop for each cell of the selected range.

**Required_Zeros = Total_Digits – Len(Cell_1.Value)**

Determine the required number of leading zeros for each cell.

**If Required_Zeros > 0 Then, Cell_1.Offset(0, 1).Value = String(Required_Zeros, “0”) & Cell_1.Value**

If any zero is required, add those zeros in the leading position of the existing cell values and paste them into the corresponding cell of the next column.

**Else, Cell_1.Offset(0, 1).Value = Cell_1.Value**

If no zero is required, then paste the existing value in the corresponding cell of the next column.

- Select
**range C5:C10**and run the code by pressing the**F5**button.

- We can see new data with leading zeros shown in the next column.

**Read More:** **How to Format Text in Excel Cell**

## How to Remove Leading Zeros in Excel

Previously, in all the methods we have shown how to add leading zeros in Excel text format. But we want to show how to remove leading zeros in Excel. For that, we will use **the VALUE function** to show the magnitude of a text.

- We have an
**ID**with two leading zeros in the updated dataset in**column C**. Insert the following formula on**cell D5**to remove leading zeros.

`=VALUE(C5)`

## How to Add Leading Zeros in Excel to Make 10 Digits

In this section, we will learn how to make a **10-digit** number by adding leading zeros. This **10-digit** number system is widely used for mobile numbers. For other cases where you need all numbers of the same **10** digits, you can follow this process.

- Simply use the formula based on the
**BASE**function to get a**10-digit**number on**cell D5**.

`=BASE(C5,10,10)`

- Then, drag the
**Fill Handle**icon.

Here, we have used **10** in the **3rd** argument to get a **10-digit** number.

## Frequently Asked Questions

** 1. What to do when leading zeros are not showing in Excel?**

**Ans:** When leading zeros are not showing in Excel, change the format of the required cells into **Text**. After that, leading zeros will be visible.

** 2. How to add 0 in front of a phone number in Excel?**

**Ans:** Follow any of the methods discussed in this article to add a zero in front of a phone number. If any phone number contains 10 digits. So, with leading zeros, that will be of 11-digits and you can use any of the following formulas.

`=BASE(C5,10,11)`

`=REPT(0,11-LEN(C5))&C5`

## Things to Remember

- Here,
**method 1**to**5**adds a fixed number of leading zeros with the given reference. But the total number of digits is not the same. **Method 6**to**11**, creates a fixed digit number by adding the required number of leading zeros. Here, the number of leading zeros is not fixed, depending on the given reference.

**Download Practice Workbook**

**Conclusion**

In this article, we described how to add leading zeros in Excel text format using multiple methods consisting of formula, without formula, VBA code, and Power query. We also mentioned that in some methods the number of leading zeros is fixed and in others, the total number of digits is the same. We also showed how to remove leading zeros from Excel. I hope this will satisfy your needs.