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

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

STEPS:

⇒ Go to Home tab >> select Number >> select Text.

⇒ Your numbers will be in Text format.

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

⇒Press ENTER. Excel will concatenate the numbers for you.

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

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

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

STEPS:

⇒ Select cell F4 and write down the formula:

⇒ Press ENTER. Excel will concatenate the numbers for you.

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

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

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

STEPS:

⇒ Select cell F4 and write down the formula;

⇒ Press ENTER. Excel will concatenate the numbers for you.

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

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

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

STEPS:

⇒ Select cell F4 and write down the formula;

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.

⇒ Press ENTER. Excel will concatenate the numbers for you.

⇒ 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 convert the numbers into Text format. So it is not mandatory to have the cell values in Text format.

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

STEPS:

⇒ Select cell F4 and write down the formula;

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

⇒ Press ENTER. Excel will concatenate the numbers for you.

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

Method 6 – Application of Power Query to Concatenate Numbers with Leading Zeros

Use Power Query to concatenate numbers with leading zeros. It 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 current.

The format will be changed to Text.

⇒ Select the Departmental Code column first and then the Serial Number column by holding the CTRL key. Excel will select both columns.

⇒ Go to Add Column >> select Merge Columns.

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

⇒ Click OK.

⇒ Excel will create a new column ID.

⇒ Go to the Home tab >> select Close & Load.

⇒ Excel will create a new table with the ID column in a new sheet.

Download Practice Workbook

Related Articles

• How to Combine Text and Number in Excel
• How to Add a 1 in Front of Numbers in Excel
• How to Concatenate and Keep Number Format in Excel
• How to Concatenate Date That Doesn’t Become Number in Excel
• How to Combine Date and Text in Excel
• How to Concatenate and Keep Currency Format in Excel
• How to Combine Text and Numbers in Excel and Keep Formatting

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