How to Concatenate Numbers with Leading Zeros in Excel (6 Methods)

Get FREE Advanced Excel Exercises with Solutions!

Excel is undoubtedly one of the most useful and mostly used tools. We can take the help of Excel to do so many things in many ways. For example, in Excel, to concatenate numbers with leading zeros, you can use six methods. In this article, I am going to discuss those six methods in Excel to concatenate numbers with leading zeros.


Download Workbook

This is the datasheet that I am going to use in Excel to concatenate numbers with leading zeros. Here, we have some student Name(s) along with their Department, Department Code, and Serial Number. I will concatenate the Department Code and Serial Number to get the ID for each student.

Excel Concatenate Numbers with Leading Zero


Six Ways to Concatenate Numbers with Leading Zeros in Excel

1. Using CONCATENATE Function in Excel to Concatenate Numbers with Leading Zeros

STEPS:

First of all, I will show you using the CONCATENATE function in Excel to concatenate numbers with leading zeros.
But before doing so, I want you to notice one thing. For this method, you have to set the format as Text to apply this formula. Because Text format allows having numbers with leading zeros. To do so,
Go to Home tab >> select Number >> select Text.

Excel Concatenate Numbers with Leading Zero

Thus your numbers will be in Text format.

Excel Concatenate Numbers with Leading Zero

Now, select cell F4 and write down the formula;

=CONCATENATE(D4,E4)

Excel Concatenate Numbers with Leading Zero

Then press ENTER. Excel will concatenate the numbers for you.

Here, Excel has concatenated the numbers in cells D4 and E4 and the result is in cell F4.

Then use Fill Handle to AutoFill up to F9. You will concatenate all the Department Code and Serial Number to get the ID of each student.

Excel Concatenate Numbers with Leading Zero

Read More: How to Add Leading Zeros in Excel Text Format (10 Ways)


2. Applying CONCAT Function in Excel to Concatenate Numbers with Leading Zeros

Now, I will show you using the CONCAT function in Excel to concatenate numbers with leading zeros. You have to change the number format to Text just like method 1.

STEPS:

Select cell F4 and write down the formula;

=CONCAT(D4,E4)

Excel Concatenate Numbers with Leading Zero

Then press ENTER. Excel will concatenate the numbers for you.

Excel Concatenate Numbers with Leading Zero

Here, Excel has concatenated the numbers in cell D4 and E4 and the result is in cell F4.

Then use Fill Handle to AutoFill up to F9. You will concatenate all the Department Code and Serial Number to get the ID of each student.

Read More: Concatenate Date and Time in Excel (4 Formulas)


3. Inserting Ampersand (&) in Excel to Concatenate Numbers with Leading Zeros

Now, I will show you using the Ampersand in Excel to concatenate numbers with leading zeros. You have to change the number format to Text just like method 1.

STEPS:

Select cell F4 and write down the formula;

=D4&E4

Excel Concatenate Numbers with Leading Zero
Then press ENTER. Excel will concatenate the numbers for you.

Excel Concatenate Numbers with Leading Zero

Here, Excel has concatenated the numbers in cell D4 and E4 with the help of ampersand and the result is in cell F4.

Then use Fill Handle to AutoFill upto F9. You will concatenate all the Department Code and Serial Number to get the ID of each student.

Read More: How to Concatenate in Excel (3 Suitable Ways)


Similar Readings:


4. Using TEXT Function in Excel to Concatenate Numbers with Leading Zeros

In this section, I will use the TEXT function to concatenate numbers with leading zeros.

STEPS:

First, select cell F4 and write down the formula;

=TEXT(D4,"00")&TEXT(E4,"000")

Excel Concatenate Numbers with Leading Zero

Here, the TEXT function converts the numbers in cells D4 and E4 to Text format. “00” and “000” indicate that the number in cell D4 will have at least two digits and the number in cell E4 will have at least three digits.

Then press ENTER. Excel will concatenate the numbers for you.

Excel Concatenate Numbers with Leading Zero

Then use Fill Handle to AutoFill up to F9. You will concatenate all the Department Code and Serial Number to get the ID of each student.


Note: In this method, Excel will ultimately convert the numbers into Text format. So it is not mandatory to have the cell values in Text format.

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


5. Use of TEXTJOIN Function in Excel to Concatenate Numbers with Leading Zeros

In this section, I will use the TEXTJOIN function to concatenate numbers with leading zeros.

STEPS:

First, select cell F4 and write down the formula;

=TEXTJOIN("",TRUE,D4,E4)


Here, we do not want any delimiter between Department Code and Serial Number. That’s why the delimiter is blank “”. Additionally, to ignore empty cells I have used TRUE in the second argument. With this formula, I will get the ID of the student in cell F4 using cells D4 and E4.

Then press ENTER. Excel will concatenate the numbers for you.

Excel Concatenate Numbers with Leading Zero
Then use Fill Handle to AutoFill upto F9. You will concatenate all the Department Code and Serial Number to get the ID of each student.

Read More: How to Concatenate Date That Doesn’t Become Number in Excel (5 Ways)


6. Application of Power Query to Concatenate Numbers with Leading Zeros

Now I am going to use Power Query to concatenate numbers with leading zeros.
Here, we will create a new column naming the ID of students by combining Departmental Code and Serial Number.Excel Concatenate Numbers with Leading Zero

STEPS:

Select entire dataset >> go to Data tab >> select From Table/Range

Excel Concatenate Numbers with Leading Zero

⇒ The Create Table window will pop up. Click OK.

Excel Concatenate Numbers with Leading Zero

Power Query Editor window will appear.

Excel Concatenate Numbers with Leading Zero

For the Departmental Code and Serial Number column, change the format to Text.


Select Replace Column.

The format will be changed to Text.

After that, select the Departmental Code column first and then the Serial Number column by holding the CTRL key. Excel will select both columns.

Then, go to Add Column >> select Merge Columns.

Then the Merge Columns window will appear. Choose the separator as None. Set the New column name as ID.

Then click OK.

Excel Concatenate Numbers with Leading ZeroExcel will create a new column ID.


Then go to the Home tab >> select Close & Load.

Excel Concatenate Numbers with Leading Zero
Excel will create a new table with the ID column in a new sheet.

Read More: How to Add Leading Zeros in Excel by CONCATENATE Operation


Practice Workbook

It is easy to concatenate numbers with leading zeros. However, without practice, it is impossible to get the hang of these methods. That’s why I have attached a practice sheet for all of you. I believe this will be helpful.


Conclusion

In this article, I have explained six methods to concatenate numbers with leading zeros. I hope these methods will greatly benefit everyone. Lastly, if you have any comments, please leave them in the comment box.

Excel with us!


Related Articles

Akib Bin Rashid

Akib Bin Rashid

Hi, this is MD Akib Bin Rashid. I completed my BSc in Engineering back in 2019. After that, I did an MBA. Then, I joined as an Excel and VBA Content Developer at SOFTEKO Digital. Being passionate about data analytics and finance, I will try to assist you with Excel

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo