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.

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.

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.

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

• Press Enter.

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

• Again, press Enter.

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

• You will get the result in column E.

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.

• Then, press Enter.

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

• Similarly, press Enter.

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

• You will get the result.

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.

• Now, press the Enter key.

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

• Again, press Enter.

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

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.

• Now, press Enter.

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

• Press Enter.

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

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!