How to Concatenate and Keep Number Format in Excel

Get FREE Advanced Excel Exercises with Solutions!

The CONCATENATE is a function that usually joins the values of multiple cells and shows that in a single cell. But, when we apply this function, all the value formats will convert into simple text formats. In the article, we will demonstrate to you 4 simple ways to use the CONCATENATE function and keep the number format in Excel. If you are also curious about it, download our practice workbook and follow us.


How to Concatenate and Keep Number Format in Excel: 4 Easy Methods

To concatenate numbers in Excel, let’s consider a dataset of 10 people and their points, which has three decimal values. Our dataset is in the range of cell B5:C14. We want to show the point value with two decimal places with their name in column D. If we apply the CONCATENATE function, it will just join them. However, the two decimal points result will not come. You can see the situation in the image shown below.

📚 Note:

All the operations of this article are accomplished by using the Microsoft Office 365 application.


1. Apply Format Cell Option to Keep Number Format

In this method, first, we will use the TEXT function to set the right format, and then we will use the CONCATENATE function to get the result. The steps of this process are given below:

📌 Steps:

  • First of all, select the range of cell C5:C14.
  • Afterward, right-click on your mouse to launch the Context Menu and choose the Format Cells option.

Opening context menu option

  • As a result, a small dialog box called Format Cells will appear.
  • Then, in the Number tab, choose the Custom option from the Category section.
  • Next, keep a copy of the current number format of the Points column.

  • Now, select cell D5.
  • Write down the following formula using the TEXT function to get the correct number format.

=TEXT(C5,"#,##0.00")

  • Press Enter.

Using TEXT Function to Apply Format Cell Option to Keep Number Format

  • After that, select cell E5 and write down the following formula using the CONCATENATE function.

=CONCATENATE(B5,"  P: ",D5)

  • Again, press Enter.

Using CONCATENATE Function Concatenate and Keep Number Format

  • Then, drag the Fill Handle icon to copy the formula upto cell E14.

  • You will get the result in column E.

Apply Format Cell Option to Concatenate and Keep Number Format

Thus, we can say that our formula works perfectly, and we are able to concatenate and keep the number format in Excel.


2. Use Ampersand Symbol to Keep Number Format

In this process, first, we are going to use the TEXT function to get the right format, and then we will use the ampersand symbol to get the result. The steps of this approach are given as follows:

📌 Steps:

  • First, select cell D5.
  • Now, write down the following formula using the TEXT function to get the correct number format.

=TEXT(C5,"#,##0.00")

  • Then, press Enter.

Applying TEXT Function Concatenate and Keep Number Format

  • Afterward, select cell E5 and write down the following formula using the ampersand symbol.

=B5 & "  P: " &D5

  • Similarly, press Enter.

Using Ampersand Symbol to Keep Number Format and Concatenate the Cell Value

  • After that, drag the Fill Handle icon to copy the formula upto cell E14.

  • You will get the result.

Use Ampersand Symbol to Concatenate and Keep Number Format

Hence, we can say that our formula works effectively, and we are able to concatenate and keep the number format in Excel.

Read More: How to Combine Text and Numbers in Excel and Keep Formatting


3. Utilizing TEXTJOIN Function to Concatenate and Keep Number Format

In this approach, we will use the TEXT and TEXTJOIN functions to concatenate and keep the number format in Excel. The steps of this procedure are explained below:

📌 Steps:

  • At first, select cell D5.
  • After that, write down the following formula using the TEXT function to get the correct number format.

=TEXT(C5,"#,##0.00")

  • Now, press the Enter key.

Using TEXT Function

  • Then, select cell E5 and write down the following formula using the TEXTJOIN function.

=TEXTJOIN("  P: ",TRUE,B5,D5)

  • Again, press Enter.

Using the TEXTJOIN Function to Get the Proper Formatting

  • Next, drag the Fill Handle icon to copy the formula upto cell E14.

  • You will be able to concatenate and keep the number for format at the same time.

Utilizing TEXTJOIN Function to Concatenate and Keep Number Format

Therefore, we can say that our formula works precisely, and we are able to concatenate and keep the number format in Excel.


4. Applying CONCAT Function to Concatenate and Keep Number Format

In the last case, the TEXT and CONCAT functions will help us to concatenate and keep the number format in Excel. The procedure is described below step-by-step:

📌 Steps:

  • Firstly, select cell D5.
  • Next, write down the following formula using the TEXT function to get the correct number format.

=TEXT(C5,"#,##0.00")

  • Now, press Enter.

Utilizing TEXT Function

  • After that, select cell E5 and write down the following formula using the CONCAT function.

=CONCAT(B5,"  P: ",D5)

  • Press Enter.

Applying CONCAT Function

  • Next, drag the Fill Handle icon to copy the formula upto cell E14.

  • You will be able to concatenate and keep the number for format at the same time.

Applying CONCAT Function to Concatenate and Keep Number Format

Finally, we can say that our formula works precisely, and we are able to concatenate and keep the number format in Excel.


Download Practice Workbook

Download this practice workbook for practice while you are reading this article.


Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to concatenate and keep the number format in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.

Keep learning new methods and keep growing!


What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Soumik Dutta
Soumik Dutta

Soumik Dutta, having earned a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a key role as an Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Soumik not only skillfully addresses complex challenges but also demonstrates enthusiasm and expertise in gracefully navigating tough situations, underscoring his unwavering commitment to consistently deliver exceptional, high-quality content that... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo