How to Add Leading Zeros in Excel by CONCATENATE Operation

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.

dataset


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.

concatenate function

➤ Press ENTER.

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

add leading zeros in excel concatenate

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.

add leading zeros in excel concatenate

➤ Press ENTER.

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

add leading zeros in excel concatenate

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

add leading zeros in excel concatenate

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.

text

Now,

➤ Type the number with leading zeros in cell C5.

data

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.

error

To remove the error sign,

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

removing error

As a result, the error sign will be gone.

data

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

add leading zeros in excel concatenate

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.

text function

➤ Press ENTER.

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

add leading zeros in excel concatenate

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.

add leading zeros in excel concatenate

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.

add leading zeros in excel concatenate

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

error

To remove the error sign,

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

ignore error

As a result, the error sign will be gone.

number

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

add leading zeros in excel concatenate

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.

the right function

➤ Press ENTER.

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

worksheet data

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.

add leading zeros in excel concatenate

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.

custom format

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.

custom type

Now,

➤ Type the number in cell C5.

entries

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.

special format

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

add leading zeros in excel concatenate


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.

the rept function

➤ Press ENTER.

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

add leading zeros in excel concatenate

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.

add leading zeros in excel concatenate


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.

the base function

➤ Press ENTER.

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

add leading zeros in excel concatenate

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.

add leading zeros in excel concatenate


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.


Related Articles

Prantick

Prantick

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo