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.

concatenating numbers with leading zeros


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

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.

Selecting cells to change their format from number to Text to write numbers with leading zeros

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

Numbers are formatted as texts write 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.

=CONCATENATE(C6,D6,E6)

concatenate numbers with leading zeros using CONCATENATE function in Excel

  • Press Enter and see the results.

Outcome of applying formula

 concatenating numbers with leading zeros

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

Selecting cells to change their format from number to Text to write numbers with leading zeros

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

Numbers are formatted as texts write 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)

concatenate numbers with leading zeros using CONCAT function in Excel

  • Press Enter and see the results.

Concatenated Outcome after using formula

 concatenating numbers with leading zeros

📕 Read More: 2 Ways to Bold Text in Concatenate Formula in Excel

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.

Selecting cells to change their format from number to Text to write numbers with leading zeros

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

Numbers are formatted as texts write 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

concatenate numbers with leading zeros using ampersand "&" in Excel

  • Press Enter and see the results.

Concatenated Outcome after using formula

 concatenating numbers with leading zeros

📕 Read More: 5 Easy Methods to Combine Rows into One Cell in Excel

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")

concatenate numbers with leading zeros using TEXT function in Excel

  • Press Enter and see the results.

Concatenated Outcome after using formula

concatenating numbers with leading zeros

📕 Read More: 5 Examples to CONCATENATE If Date Becomes Number in Excel

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.

Selecting cells to change their format from number to Text to write numbers with leading zeros

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

Numbers are formatted as texts write 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)

concatenate numbers with leading zeros using TEXTJOIN function in Excel

  • Press Enter and see the results.

Concatenated Outcome after using formula

 concatenating numbers with leading zeros

📕 Read More: 6 Ways to Concatenate with Carriage Return in Excel Formula

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.Opening up Power Query editor
  • A small box will appear as the picture below just click OK as you have already declared your table by selecting.

Selecting table to open up Power Query editor

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

Microsoft Power Query Editor

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

Changing format from numbers to texts

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

Confirming to replace the current column changing format

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

Numbers stores as texts

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

Adding a new column by merging other columns

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

Renaming the new column

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

Created a new column by merging multiple columns

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

Getting the results from Power Query editor to the worksheet

  • Here is the result.

Final results for concatenating numbers with leading zeros in excel


📄 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

(Visited 43 times, 1 visits today)
Nashid

Nashid

Hello there. I am Nashid, an Engineer. I Love music, traveling, sports and gaming. I have recently grown interest in DATA SCIENCE and wish to build a career over it. Excel is one of the greatest modern digital creation of human. Life simply gets easier. My Articles are meant to lead you to that easier life with efficiency. If you need any kind of help regarding my articles even anything else try contacting me on social platforms of which I've provided. Keep learning. Wish me luck and Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo