# 6 Ways to Concatenate Numbers with Leading Zeros in Excel

In this lesson, I’ll demonstrate 6 different approaches to concatenate numbers with leading zeros in Excel. Moreover, these techniques will enable you to solve the problem that led you here. However, you will master several crucial Excel features and functions during this article, which will come in extremely handy for any assignment using Excel.

## Learn to Concatenate Numbers with Leading Zeros in Excel with These 6 Useful Methods

The main idea behind concatenating numbers with leading zeros is to just concatenate them keeping the zeros. Excel offers a handful of methods for concatenation that we are going to apply in this section. Some of them include the usage of different functions and operators. We are also going to utilize the power query to do the same.

Method 1

### 1. Oldschool CONCATANATE Function

The CONCATANATE Function is a text function that allows you to combine 2 or many text strings into a single string. We will be using this function to create Student IDs of a University combining different codes. Follow the steps below.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, select the cells where you want to input numbers with zeros at front.

• After that change their format to TEXT from Home tab and type in your preferred numbers with leading zeros. The errors are just notifying you that numbers are as texts nothing to worry.

• Now after changing them to text you will see the zeros written. Simply copy the following formula in the cell where you want to concatenate the numbers.

=CONCATENATE(C6,D6,E6)

• Press Enter and see the results.

📕 Read More: 11 Easy Ways to Concatenate Rows in Excel

Method 2

### 2. Using CONCAT Function

Here in this section we will be using the newer CONCAT Function which is a substitute for the CONCATENATE function. This function is basically the same as the CONCATENATE function, the difference is, The CONCAT function can combine all of the strings that are contained inside a given range. You won’t need to choose each cell one at a time thanks to this handy feature.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, select the cells where you want to input numbers with zeros at front.

• After that change their format to TEXT and type in your preferred numbers with leading zeros.

• Now after changing them to text you will see the zeros written. Simply copy the following formula in the cell where you want to concatenate the numbers.

=CONCAT(C6:E6)

• Press Enter and see the results.

Method 3

### 3. Utilizing Ampersand “&” Sign

In this part we will be using the Ampersand sign&” to concatenate numbers with leading zeros. With the help of the Ampersand (&) an arithmetic symbol, you can connect pieces of text beyond necessitating the need to use a function. Follow the instructions below.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, select the cells where you want to input numbers with zeros at front.

• After that change their format to TEXT and type in your preferred numbers with leading zeros.

• Now after changing them to text you will see the zeros written. Simply copy the following formula in the cell where you want to concatenate the numbers.

=C6&D6&E6

• Press Enter and see the results.

Method 4

### 4. Using TEXT Function

Inside a worksheet, the TEXT Function is utilized to do a conversion from numerical values to textual ones. The function will, in its most basic form, transform a numerical value into a textual string. The TEXT Function can be found in each and every version of Excel. We don’t need to change the format in this method. Follow the instructions below to concatenate numbers with leading zeros using this function.

⬇️⬇️ STEPS ⬇️⬇️

• Simply copy the following formula in the cell where you want to concatenate the numbers.

`=TEXT(C6,"0000")&TEXT(D6,"00")&TEXT(E6,"000")`

• Press Enter and see the results.

Method 5

### 5. Applying TEXTJOIN Function

Text from broad types and/or characters can be merged using the TEXTJOIN Function , which also allows you to define a delimiter that will appear between the individual text values to be merged. Whereas if the delimiter is a blank textual string, then this operation will properly concatenate the ranges even if the ranges are separated by the delimiter. We are combining codes to create a Student ID here down below with this function.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, select the cells where you want to input numbers with zeros at the front.

• After that change their format to TEXT and type in your preferred numbers with leading zeros.

• Now after changing them to text you will see the zeros written. Simply copy the following formula in the cell where you want to concatenate the numbers.

=TEXTJOIN(“”,TRUE,C6,D6,E6)

• Press Enter and see the results.

Method 6

### 6. Using Power Query After Creating Table

In this approach we are going to use the Power Query feature of Microsoft to concatenate numbers with leading zeros. Follow the instructions step by step to learn.

⬇️⬇️ STEPS ⬇️⬇️

• First, select the whole dataset including the Headers then Click From Table/Range option from the Data tab.
• A small box will appear as the picture below just click OK as you have already declared your table by selecting.

• After that your Power Query Editor window will appear showing the your dataset. Now you can play around with all the options available.

• Next Click on the 123 written part of the Header which is basically the format and change it to Text as I have shown in the picture.

• The Power Query Editor asks you if you want to keep the old formatted column or just replace. Hit the Replace Current option as we don’t need a separate column for this.

• As you can see the data is left aligned now which means they are all in text format. Also the 123 sign is now showing ABC which stands for texts.

• Select the columns you want to merge then go to Add Column and select the Merge Columns option as the picture.

• A new window will pop up rename your new column and hit OK.

• We have created a new column which is the combination of codes for IDs in the Power Query Editor.

• Now go to Close & Load option from Home to get these data in the worksheet.

• Here is the result.

## 📄 Important Notes

`🖊️`  Use shortcuts to reduce the time for instance F4 for absolute value

`🖊️`  While writing formulas carefully see the suggestions and don’t forget the commas and parentheses.

`🖊️`  Be careful while defining criteria to be accurate as the dataset.

`🖊️`  While copying any formula try pasting it in the formula bar instead of the cell itself.

`🖊️`  If the data is left aligned it indicates that the data is in text format on other hand right alignment indicates to be in number format.

`📌`  The article demonstrated different functions to concatenate numbers with leading zeros in Excel.

`📌`  In the first section, we showed how to use CONCATENATE function to  concatenate numbers with leading zeros in an Excel dataset.

`📌`  Then, we demonstrated how to use the CONCAT, AMPERSAND, TEXT and TEXTJOIN function to concatenate numbers with leading zeros in Excel.

`📌`  After that we used Power Query method after creating table where to concatenate numbers with leading zeros.

`📌`  Finally, we properly learned how to concatenate numbers with leading zeros with different approaches.