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.

## 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 IDs will be single digits, and the rest are double digits. To show all the IDs of the same digits we add a leading zero for 1 to 9 and all IDs 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 IDs 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 the**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.

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

**Read More: **How to Add Leading Zeros in Excel

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

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

**Read More: **How to Add Leading Zeros to Make 10 Digits in Excel

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

**Related Articles**

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