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

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.

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.

Thus your numbers will be in Text format.

⇒ Now, select cell F4 and write down the formula;

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

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;

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

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;

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

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:

• Concatenate Rows in Excel (11 Methods)
• Combine Text in Excel (8 Suitable Ways)
• Carriage Return in Excel Formula to Concatenate (6 Examples)
• Concatenate Multiple Cells but Ignore Blanks in Excel (5 Ways)
• How to Concatenate Multiple Cells with Comma in Excel (4 Ways)

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;

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.

⇒ 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;

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.

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

STEPS:

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

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

Power Query Editor window will appear.

⇒ 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 will create a new column ID.

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

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

• Keep Leading Zeros in Excel CSV (4 Easy Ways)
• How to Concatenate String and Variable in Excel VBA (A Detailed Analysis)
• Macro to Concatenate Multiple Columns in Excel (with UDF and UserForm)
• How to Concatenate Multiple Cells With Space in Excel (7 Methods)
• [Solved]: Leading Zero Not Showing in Excel (9 Possible Solutions)
• How to Put 0 in Excel in Front of Numbers (5 Handy Methods)