In Excel, if you insert a number with leading zeros, the zeros will disappear. Excel does that because normally zeros in front of a number don’t convey any meaning. But for some special cases such as for phone numbers or product codes you may need to insert numbers with leading zeros. To **add leading zeros** in Excel, you have to apply some special techniques such as using **the CONCATENATE function**. In this article, I’ll show you 8 methods by which you will be able to add leading zeros in Excel concatenate operation.

Suppose, you have some product number in your dataset. Now, you want to make a 6 digit product code by adding leading zeros in the product number.

**Table of Contents**hide

## Download Practice Workbook

## 8 Methods to Add Leading Zeros in Excel by Concatenate Operation

### 1. CONCATENATE Function to Add Leading Zeros

With the **CONCATENATE **function, you can add two or more texts together. So, you can add leading zeros in a number or text with this function. First,

➤ Type the following formula in cell **C5**,

`=CONCATENATE("0000",B5)`

There is a 2 digit number in cell **B5**. The formula will add 4 leading zeros with this number and return the output in cell **C5**.

➤ Press **ENTER**.

As a result, you will get the 6 digit code in cell **C5**.

Now, for the number in cell **B6**,

➤ Type the following formula in cell **C6**,

`=CONCATENATE("000",B6)`

There is a 3 digit number in cell **B6**. The formula will add 3 leading zeros with this number and return the output in cell **C6**.

➤ Press **ENTER**.

As a result, you will get the 6 digit code in cell **C6**.

In a similar manner, you can concatenate leading zeros with the other numbers.

For your information, you can do exactly the same thing with **the CONCAT function**.

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

### 2. Add Leading Zeros by Text Format

By setting the cell format into **Text format**, you can **add leading zeros** in the input of the cell. First,

➤ Select the cells where you want to add leading zeros.

After that,

➤ Select **Text **in the box of the **Number **ribbon of the **Home **tab.

Now,

➤ Type the number with leading zeros in cell **C5**.

After pressing **ENTER**, you’ll see an **Error sign** will appear beside the cell. You’ve entered a number in the cell, but the cell is formatted as text. So, Excel is warning you in case you have entered the data by mistake.

To remove the error sign,

➤ Click on the sign and select **Ignore Error **from the dropdown menu.

As a result, the error sign will be gone.

In a similar manner, you can add the other codes with the leading zeros.

**Read More:** **Excel Convert Number to Text with Leading Zeros: 10 Effective Ways**

### 3. TEXT Function to Add Leading Zeros

With **the TEXT function**, you can enter data in a cell in any specific format. In this method, I’ll show you how you can concatenate leading zeros with any number or text. First,

➤ Type the following formula in cell **C6**,

`=TEXT(B5,"000000")`

The formula will return the data of cell **C5 **in **“000000” **format. If the number of digits of the input number is less than 6, leading zeros will be added in such a way that the number will have a total of 6 digits. So, if you insert a two digit number in this formula, 4 leading zeros will be added.

➤ Press **ENTER**.

As a result, you will get the 6 digit code with leading zeros in cell **C5**.

Now,

➤ Drag cell **C5** to the end of your dataset.

So, you will get 6 digit codes with leading zeros for all of the product numbers.

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

### 4. Leading Apostrophe to Add Zeros

By adding a leading **apostrophe **(**‘**) you can concatenate leading zeros before any string.

➤ Type an apostrophe and then type the number with leading zeros.

As a result, the number with the leading zeros will be inserted in the cell.

After pressing **ENTER**, you’ll see an **Error sign** will appear beside the cell stating that “ The number in this cell is formatted as text or preceded by an apostrophe.”

To remove the error sign,

➤ Click on the sign and select **Ignore Error **from the dropdown menu.

As a result, the error sign will be gone.

In a similar way, you can insert all the other numbers with leading zeros.

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

### 5. Using the RIGHT Function

You can also concatenate leading zeros to a number or text by using **the RIGHT function****. **First,

➤ Type the following formula in cell **C5**,

`=RIGHT("000000"&B5,6)`

The formula will concatenate leading zeros with the number of cell **B5**. The syntax **6 **in the formula indicates that there will be a total of 6 digits in the output. So here, the formula will concatenate 6 zeros with the number of cell **B5** and then return the six digits from the right end.

➤ Press **ENTER**.

As a result, you will get the 6 digit code with leading zeros in cell **C5**.

Now,

➤ Drag cell **C5** to the end of your dataset.

So, you will get 6 digit codes with leading zeros for all of the product numbers.

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

### 6. Custom Format to Add Leading Zeros

You can make a **custom format** to add leading zeros before a number. First,

➤ Select the cells where you want to add leading zeros.

After that,

➤ Select **More Number Formats **in the box of the **Number **ribbon of the **Home **tab.

As a result, the **Number **tab of the **Format Cells **window will appear. Now,

➤ Select **Custom **in the **Category **box and then insert **000000** (the format for the selected cells) in the **Type **box.

After that,

➤ Press **OK**.

Now,

➤ Type the number in cell **C5**.

If you observe the **Number **ribbon of the **Home **tab after inserting the number, you will see **Special **is selected as number format. This is appearing because of your custom format.

Now, in a similar way, you can insert all the other numbers with leading zeros.

### 7. Utilizing REPT Function to Add Leading Zeros in Excel

You can also concatenate leading zeros with a string by using **the REPT function** and **the LEN function** altogether. First,

➤ Type the following formula in cell **C5**,

`=REPT(0,6-LEN(B5))&B5`

Here, **0 **in the argument indicates that **0 **will be returned repeatedly, **6-LEN(B5) **portions of the formula determines how many times **0 **will be returned. Finally, the formula will concatenate the returned zeros with the string of cell **B5**.

➤ Press **ENTER**.

As a result, you will get the 6 digit code with leading zeros in cell **C5**.

Now,

➤ Drag cell **C5** to the end of your dataset.

So, you will get 6 digit codes with leading zeros for all of the product numbers.

### 8. Add Leading Zeros in Excel with BASE Function

Using **the BASE function** is another way to add leading zeros in a string. First,

➤ Type the following formula in cell **C5**,

`=BASE(B5,10,6)`

Here **10 **in the argument indicates that the returned number will be 10 based that means it will be a decimal number and **6 **in the argument indicates that there will be a minimum of 6 digits in the returned number.

➤ Press **ENTER**.

As a result, you will get the 6 digit code with leading zeros in cell **C5**.

Now,

➤ Drag cell **C5** to the end of your dataset.

So, you will get 6 digit codes with leading zeros for all of the product numbers.

## Conclusion

I hope now you will be able to add leading zeros in Excel by concatenate operation. If you follow the methods 1,2,4,5 of this article, you have to insert each entry separately. Though it is more time-consuming, you may need to follow these methods in case of customized entries. On the other hand, methods 3,6,7,8 of this article create a dynamic formula that you can apply for all the entries of a column. If you have any kind of confusion about any of the methods please leave a comment.