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

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

STEPS:

• Go to the Home tab. Select Number and choose Text from the drop-down.

Your numbers will be in Text format.

• Select cell F4 and insert the following formula:

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

• Use the Fill Handle to AutoFill to F9.

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

STEPS:

• Select cell F4 and insert the formula:

• Press ENTER.

• Use the Fill Handle to AutoFill up to F9.

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

STEPS:

• Select cell F4 and enter the following formula:

• Press ENTER.

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

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

STEPS:

• Use the following formula in F4:

• Hit Enter.

• Use the Fill Handle to AutoFill to F9.

Note: In this method, Excel will convert the numbers into Text format.

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

STEPS:

• Enter the following formula in F4:

• Press ENTER.

• Use the Fill Handle to AutoFill to F9.

Method 6 – Using Power Query to Concatenate Numbers with Leading Zeros

We will create a new ID column by combining Departmental Code and Serial Number.

STEPS:

• Select the entire dataset, go to Data tab, and select From Table/Range

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

• The Power Query Editor window will appear.

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

• Select Replace current.

• Select the Departmental Code and the Serial Number columns.
• Go to Add Column and select Merge Columns.

• The 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 and select Close & Load.

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

Download the 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