This is the sample dataset.

### Method 1 – Applying the Text Format to Add Leading Zeros

**Steps:**

- Select the cells to add leading zeros. Here,
**C5:****C13**. - Go to the
**Home tab**and select**Number.** - Choose
**General**.

- Select
**Text**.

- Enter the numbers with leading zeros.

An error is displayed: numbers are stored as text.

- Select
**C5:C13**.

- Click the
**Error Warning**. - Select
**Ignore Error**.

This is the output.

**To use these numbers in calculations, change the format to**

*Note:***Number**.

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

### Method 2 – Entering Leading Zeros with a Custom Format in Excel

**Steps:**

- Select the cells to add leading zeros. Here,
**C5:****C13**. - Right-click and select
**Format Cells**.

- Select
**Custom**in**Category**. - Enter a format.
- Click
**OK**.

This is the output.

### Method 3 – Using the Apostrophe (`'`

) to Add Leading Zeros

**Steps:**

- Select the cell to add leading zeros. Here,
**C5**. - Enter an
**Apostrophe (**before the number with leading zeros.`'`

)

- Press
**Enter**to see the result.

- Follow the steps above to add leading zeros to the other numbers.

- Remove the
**Error Warning**by following the steps described in**Method 1**.

**Read More:** How to Write 00 in Excel

### Method 4 – Adding Leading Zeros with the TEXT Function

The syntax of the **TEXT** function is,

`=TEXT(value,format_text)`

**Steps:**

- Select the cell to add leading zeros. Here,
**D5**. - Enter the following formula.

`=TEXT(C5,"000000")`

- Press
**Enter**to see the result.

**C5**is the value and

**“000000”**refers to

**format_text**.

- Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

### Method 5 – Using the Ampersand (&) Operator in Excel

**Steps:**

- Select the cell to add leading zeros. Here,
**D5**. - Enter the following formula.

`="000"&C5`

- Press
**Enter**to see the result.

**Ampersand (&)**operator adds

**“000”**to the value in

**C5**.

- Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

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

### Method 6 – Using the CONCATENATE Function

The syntax of the **CONCATENATE** function is:

`=CONCATENATE(text1,text2,...)`

**Steps:**

- Select the cell to add leading zeros. Here,
**D5**. - Enter the following formula.

`=CONCATENATE("000",C5)`

- Press
**Enter**to see the result.

**“000”**is selected as

**text1**and

**C5**as

**text2**.

- Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

### Method 7 – Adding Leading Zeros with the RIGHT Function in Excel

The syntax of the **RIGHT** function is:

`=RIGHT(text,num_char)`

**Steps:**

- Select the cell to add leading zeros. Here,
**D5**. - Enter the following formula.

`=RIGHT("00000"&C5,6)`

- Press
**Enter**to see the result.

**Formula Breakdown**

**“00000”&C5:**joins the texts and returns a single text.**RIGHT(“00000″&C5,6):**returns 6 characters from the right side of the text.

- Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

### Method 8 – Using the RIGHT and the CONCATENATE Functions in Excel

**Steps:**

- Select the cell to add leading zeros. Here,
**D5**. - Enter the following formula.

`=RIGHT(CONCATENATE("00000",C5),6)`

- Press
**Enter**to see the result.

**Formula Breakdown**

**CONCATENATE(“00000”,C5):**joins the two texts.**RIGHT(CONCATENATE(“00000”,C5),6):**returns 6 characters from the right side of the text.

- Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

### Method 9 – Adding Leading Zeros with the Excel BASE Function

The syntax of the **BASE** function is:

`=BASE(number, radix, min_length)`

**Steps:**

- Select the cell to add leading zeros. Here,
**D5**. - Enter the following formula.

`=BASE(C5,10,6)`

- Press
**Enter**to see the result.

**C5**is selected as

**number**, 10 as the

**radix**, and 6 as

**min_length**. The formula converts the number in

**C5**to base 10 (decimal) with a minimum length of 6.

- Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

### Method 10 – Using the CONCATENATE, REPT, and LEN Functions to add Leading Zeros

The syntax of the **REPT** function is:

`=REPT(Character,Times_to_repeat) `

The syntax of the **LEN** function is:

`=LEN(text)`

**Steps:**

- Select the cell to add leading zeros. Here,
**D5**. - Enter the following formula.

`=CONCATENATE(REPT(0,6-LEN(C5)),C5)`

- Press
**Enter**to see the result.

** Formula Breakdown**

**LEN(C5):**returns the length of the value in**C5**.**6-LEN(C5):**the length of**C5**is subtracted from**6**.**REPT(0,6-LEN(C5)):**repeats**0**the number of times specified by the previous calculations.**CONCATENATE(REPT(0,6-LEN(C5)),C5):**joins the zeros and the value in**C5**.

- Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

### Method 11 – Using VBA to add Leading Zeros

**Steps:**

- Go to the
**Developer**tab. - Select
**Visual Basic**to open the**Visual Basic Editor**window. You can also click**Alt + F11**.

- Click
**Insert**. - Choose
**Module**.

- Enter the following code in the 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
```

** Code Breakdown**

- The
**Add_Leading_Zeros**function is created. **ref_num**, and**str_length**are the two arguments.- It returns an output with a specified number of leading zeros (the total length of the numbers is
**str_length**).

- Save the code and go back to your worksheet.
- Select
**D5**. - Enter the following formula:

`=Add_Leading_Zeroes(C5,6)`

- Press
**Enter**to see the result.

**C5 **is selected as **ref_num** and **6 **as **str_length**.

- Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

## Practice Section

Practice here.

**Download Practice Workbook**

Download the practice workbook.

## Related Articles

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