How to Add Leading Zeros in Excel by CONCATENATE Operation

Get FREE Advanced Excel Exercises with Solutions!

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


1. Using Excel 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 the same thing with the CONCAT function.

Read More: How to Concatenate Numbers with Leading Zeros in Excel


2. Selecting Text Format to Add Leading Zeros in Excel

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


3. Inserting Excel TEXT Function to Include 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


4. Inserting Apostrophe to Concatenate Leading 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


5. Using RIGHT Function to Join Leading Zeros in Excel

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


6. Applying Custom Format in Excel 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. Using REPT Function to Join Leading Zeros in Excel

You can also concatenate leading zeros with a string by using REPT and LEN functions 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. Adding Leading Zeros with Excel 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


Download Practice Workbook


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


<< Go Back to Concateante Numbers in Excel | Concatenate Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Prantick Bala
Prantick Bala

PRANTICK BALA is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, he has transitioned into the role of a content developer. Beyond his work, find him immersed in books, exploring new... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo