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.

**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: **Excel Custom Number Format Multiple Conditions

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

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

**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: **Add or Keep Leading Zeros in Excel (10 Suitable Ways)

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

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

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

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

## Further Readings

- How to Remove Leading Zeros in Excel (7 Easy Ways + VBA)
- Excel 2 Decimal Places without Rounding (4 Efficient Ways)
- Excel Round to Nearest 100 (6 Quickest Ways)
- How to Round up Decimals in Excel (4 Simple Ways)
- How to Round Off Numbers in Excel (4 Easy Ways)
- How to Round to Nearest Multiple of 5 in Excel (3 Easy Ways)
- Excel Number Stored As Text [4 Smart Ways]
- Using Excel to Round to Nearest 1000 (7 Easy Ways)
- How to Round Numbers in Excel Without Formula (3 smart ways)
- How to Apply Accounting Number Format in Excel! (Easy way)