While working in Excel, we often have to add leading zeros before one or more numbers. This is in fact quite easy to implement. Today I will be showing all possible ways how you can achieve that.

**Download Practice Workbook**

## How to Add Leading Zeros in Excel

Let us have a look at this data set. We have the Employee Record of a Company named Star Group.

There are** Employee Names** in Column **B**, and their **IDs** in column **C**.

We see the IDs of each employee consist of three digits. But we want to add leading zeros in front of them to make them six digits.

How can we do that?

You can use the following methods.

**1. Use Excel Toolbar Options to Add Leading Zeros in Excel**

You can use Excel Toolbar options to insert leading zeros before any number. You can do this in two formats. **Text** Format and **Custom** Format.

**Case 1: Text Format**

- Select the range of cells where you want to insert leading zeros. Here I select cells
**C4 to C12**. - Go to
**Home>Genera**l option in Excel Toolbar under**Number**Section.

- Click on the drop-down menu. You will get these options. Scroll down and click on the last option,
**Text.**

- You will get the numbers aligned left. That means they are
**converted to texts**. Now double click on them and insert the required number of leading zeros in the front.

- You see a small
**Error Box**on the left of the first cell. Click on it. From the options available, click on**Ignore Error.**

- Do it for all the cells. Now you have inserted leading zeros before all the numbers.

**Note:** Now they are in **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)**

**Case 2: Custom Format**

- Select the cells and again go to
**Home>General**option from under**Number**Section in Excel Toolbar (See Section 1.1) - This time click on
**More Number Formats.**

- You will get a dialogue box called
**Format Cells.**In the**Category**option, select**Custom.**And in the**Type**option, write**“000000”**

- Then click
**OK**. You will get leading zeros inserted before the selected the contents of the selected cells, like this.

**Read More:** **Add or Keep Leading Zeros in Excel (10 Suitable Ways)**

**2. Use Leading Apostrophe(‘) ****to Add Leading Zeros in Excel**

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.

Like section 1.1, you will get an **Error box** on the left. You have to select **Ignore Error** from there.

This is a much better method for small and simple data set.

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

**3. Use Excel’s Functions ****to Add Leading Zeros in Excel**

**Method 1: Using TEXT() Function**

The syntax of **TEXT()** function is

`=TEXT(value,format_text)`

- Here value is the number which you want to make a text.
- And format_text is the format in which you want to have your text.

If you want to know more about the **TEXT()** function visit this **link**.

Here, we want **C4** to be 000111. So our formula will be

`=TEXT(111,“000000”)`

Then we drag the** Fill Handle** to do the same with the rest of the cells.

**Read More:** **How to Put 0 in Excel in Front of Numbers (5 Handy Methods)**

**Method 2: Using RIGHT() and CONCATENATE() Function**

The Syntax of **CONCATENATE()** function

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

- It takes two or more texts as input.
- And returns their joint text as output.

If you want to know more about **CONCATENATE()** Function, visit this **link**.

As we want to join “000” with C4 our formula will be

`=CONCATENATE("000",C4) `

Now we drag the Fill Handle to fill the rest of the cells.

We can also do the same operation by using Ampersand Symbol (&). Ampersand Symbol (&) does the same function as **CONCATENATE().**

The formula then will be:

`="000"&C4`

Obviously, if you have four digits in an ID, and you want to make it to 6 digits, you have to add “00” in place of “0000”.

We can do it by combining it with **RIGHT()** Function.

The syntax for **RIGHT()** function is

`=RIGHT(Text,num_char)`

- It takes a text and a number as input.
- And returns the text with the specified number of characters from the right as output.

If you want to know more about the **RIGHT()** Function, visit this **link**.

So, if we use more than three zeros in the** CONCATENATE()** Function, we can use the **RIGHT() **Function to bring it back to three.

The formula will be:

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

Or you can also use:

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

**Read More:** **How to Add Leading Zeros in Excel by CONCATENATE Operation**

**Method 3: Using Base() Function**

The syntax for BASE() function is

`=BASE(Value,Base,Number_of_digits)`

It takes a value and the base value of the number system as input and returns it in text format with the specified number of digits.

If you want to know more about the **BASE()** Function, visit this **link**.

As we want cell C4 to have three zeros before it, our function will be

`=BASE(C4,10,6)`

And then we drag the **Fill Handle** to fill the rest of the cells.

**Note: BASE()** Function is only available in **Office 365.**

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

**Method 4: Using CONCATENATE(), REPT() and LEN() Function**

**REPT() **Function repeats a character a specified number of times.

Syntax for **REPT()** Function is

`=REPT(Character,Times_to_repeat)`

If you want to know more about **REPT()** Function, visit this **link**.

And the **LEN()** function returns the length of any text.

Syntax for **LEN()** Function

`=LEN(text)`

So the formula

`=REPT(0,6-LEN(C4))`

Will return three zeros. And we can again CONCATENATE it with C4 to get 000111.

So the total formula will be

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

**Read More:** **How to Concatenate Numbers with Leading Zeros in Excel (6 Methods)**

**4. Use VBA Code ****to Add Leading Zeros in Excel**

You can also use this VBA Code to add leading zeros in Excel.

- Click
**Alt + F11**on your keyboard. It will open the VBA (Visual Basics for Application) window in your Excel file. - In the VBA toolbar, click on
**Insert**. Then click on**Module.**

And you will get a window like this.

- Write the following code there.

**Code**

```
Function AddLeadingZeroes(ref As Range, Length As Integer)
Dim i As Integer
Dim Result As String
Dim StrLen As Integer
StrLen = Len(ref)
For i = 1 To Length
If i <= StrLen Then
Result = Result & Mid(ref, i, 1)
Else
Result = "0" & Result
End If
Next i
AddLeadingZeroes = Result
End Function
```

- It creates a function called
**AddLeadingZeros** - The Function takes two arguments, a range of cells named
**Range**, and an integer named**Length**. - It returns an output with a specified number of leading zeros to the numbers (total length of the numbers will be
**Length)**given as input.

This **site** has helped us understand and develop this procedure.

**Read More:** **How to Remove Leading Zeros in Excel (7 Easy Ways + VBA)**

**Conclusion**

These are the ways you can use to insert 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)**