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. ## 8 Methods to Add Leading Zeros in Excel by Concatenate Operation

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.

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

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. 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. ➤ 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. ### 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. You can make a custom format to add leading zeros before a number. First,

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