Nashid
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.
📁 Download Excel File
The sample worksheet that was used during the discussion is available to download for free right here.
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.
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.
- Copy down the formula along the column for the whole table.
📕 Read More: 11 Easy Ways to Concatenate Rows in Excel
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.
- Copy down the formula along the column for the whole table.
📕 Read More: 2 Ways to Bold Text in Concatenate Formula in Excel
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.
- Copy down the formula along the column for the whole table.
📕 Read More: 5 Easy Methods to Combine Rows into One Cell in Excel
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.
- Copy down the formula along the column for the whole table.
📕 Read More: 5 Examples to CONCATENATE If Date Becomes Number in Excel
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.
- Copy down the formula along the column for the whole table.
📕 Read More: 6 Ways to Concatenate with Carriage Return in Excel Formula
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.
📝 Takeaways from This Article
📌 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.
Conclusion
Firstly, I hope you were able to use the techniques I demonstrated in this Concatenate Numbers with Leading Zeros in Excel lesson. As you can see, there are a lot of options on how to do this. However, decide deliberately on the approach that best addresses your circumstance. Most importantly, I advise repeating the steps if you become confused in any of the steps if you get stuck. However, practice on your own after taking a look at the Excel file in the practice workbook I’ve provided above because practice makes a man perfect. Above all, I earnestly hope you can use it. Please share your experience with us in the comment box about how your experience was throughout the article and what we need to improve or add to make things better for you folks. However, if you have any problem regarding Excel spit it in the comment box. The Excelden crew is always available to answer your questions. In conclusion, keep educating yourself by reading. For more Excel-related articles please visit our website Excelden.com.
Related Articles
- 2 Steps to Concatenate Multiple Results with INDEX-MATCH in Excel
- 7 Ways to Concatenate Multiple Cells with Space in Excel
- 5 Easy Ways to Add Comma in Excel to Concatenate Cells
