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.


This is the datasheet that I am going to use to concatenate numbers in Excel 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


How to Concatenate Numbers with Leading Zeros in Excel: 6 ways

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 by CONCATENATE Operation


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.


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.


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 in Excel Text Format


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.


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

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.


Practice Section

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.


Download Practice Workbook


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.


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.
Akib Bin Rashid
Akib Bin Rashid

AKIB BIN RASHID, a materials and metallurgical engineer, is passionate about delving into Excel and VBA programming. To him, programming is a valuable time-saving tool for managing data, files, and internet-related tasks. Proficient in MS Office, AutoCAD, Excel, and VBA, he goes beyond the fundamentals. Holding a B.Sc in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, MD AKIB has transitioned into a content development role. Specializing in creating technical content centred around Excel and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo