Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Concatenate and Keep Number Format in Excel

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.


Download Practice Workbook

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


4 Easy Methods to Concatenate and Keep Number Format in Excel

To demonstrate the approaches, we 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.

Read More: Concatenate Numbers in Excel (4 Quick Formulas)


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: Concatenate Multiple Cells Based on Criteria in Excel (4 Methods)


Similar Readings


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.

Read More: How to Concatenate Multiple Cells With Space in Excel (7 Methods)


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.

Read More: Combine Multiple Columns into One Column in Excel


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.

Don’t forget to check our website, ExcelDemy, for several Excel-related problems and solutions. Keep learning new methods and keep growing!


Soumik Dutta

Soumik Dutta

Hi! I am Soumik. I have completed my Bachelor of Science in Naval Architecture and Marine Engineering. I have found my calling in Machine Learning and Data Science, and in pursuing so, I have realized the importance of Data Analysis. Excel is one excellent tool to fulfill the purpose. I am always trying to learn every day about different features of Excel and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo