**Method 1 – Add Leading Zeros Manually in Excel**

** ****Steps:**

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

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

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

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.

- No error is shown now.

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

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

You can see error notifications with all cells. You can remove all those errors as shown in Method 1.

**Method 3 – Adding Leading Zeros in Excel Text Format with Ampersand**

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

`="00"&C5`

- Drag the
**Fill Handle**to the rest of the cells.

Two leading zeros have been added.

**Method 4 – TEXTJOIN Function Add Leading Zeros in Excel Text Format**

- Insert the following formula in cell
**D5**based on the**TEXTJOIN**function and pull the**FillHandle**icon**.**

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

Leading zeros have been added.

**Method 5 – Excel CONCAT Function Adds Leading Zeros**

- Input the following formula in cell
**D5**and drag the**Fill Handle**

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

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

- Insert the following formula in cell
**D5**.

`=TEXT(C5,"00000")`

- Drag the
**Fill Handle**icon to cell**D11**.

**Method 7 – Excel RIGHT Function to Add Leading Zeros**

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

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

- Drag the
**Fill Handle**icon.

**Formula Explanation**

**“0000”&C5**

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

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

- Insert the following formula in cell
**D5**and 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**

**Method 9 – Excel BASE Function Adds Leading Zeros**

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

`=BASE(C5,10,5)`

The **BASE **function converts **C5 **into a 10-base number with **5** digits.

**Method 10 – Excel Power Query to Add Leading Zeros**

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

- The
**Power Query**window and**ID**column are presenting data in number form.

- 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. - 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.** - Insert the following formula in the
**Custom column formula**section and press**OK**.

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

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

**Method 11 – Excel VBA to Add Leading Zeros**

** ****Steps:**

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

- 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

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

`=VALUE(C5)`

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

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

`=BASE(C5,10,10)`

- Drag the
**Fill Handle**icon.

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

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

## Things to Remember

**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. The number of leading zeros is not fixed, depending on the given reference.

**Download Practice Workbook**

**Related Articles**

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