How to Concatenate and Keep Currency Format in Excel (3 Methods)

While concatenating two or more pieces of text in Excel, you will find that the different number formats like currency, decimal format, and percentage format are removed and have been shown as general number format in the concatenated text. We can get around this problem using a very simple and easy trick. In this article, I will show you how to keep currency format in concatenate using different concatenation formulas.


Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.


3 Methods on How to Concatenate and Keep Currency Format in Excel

Let’s assume a scenario where we have an Excel file that contains information about different fruits and their prices. We have concatenated the name of each fruit with its associated price. While doing so, we have found that the prices of the fruits have been shown in general number format in the Incorrect Concatenation column. I will use this worksheet to show you how to keep currency format in concatenation. The image below shows the worksheet we are going to work with that has also the correct concatenation with intact currency format.

How to Keep Currency Format in Concatenate


Method 1: Apply Format Cell Option and Keep Currency Format

To keep the currency format in concatenation, we have to change the format of the cells. Let’s see how we can do that.

Step 1:

  • First, we will select all the cells in the Price column except for the column header. We will then right-click on any of the selected cells.
  • The context menu will appear. Now, we will click on the Format Cells from the context menu.

Apply Format Cell Option and Keep Currency Format

Step 2:

  • A new window titled Format Cells will appear. Under the Number tab, we will click on the Custom from the Category.
  • Now, we will see different varieties of number formats on the right side of the Format Cells. Then, we will scroll down the window and select the $#,##0.00_);($#,##0.00) format from the Type of number formats.

Apply Format Cell Option and Keep Currency Format

Step 3:

  • Now, we will write down the below formula in cell D5.
=TEXT(C5,"$#,##0.00_);($#,##0.00)")

Formula Breakdown:

The TEXT formula will format the price (C5) using the $#,##0.00_);($#,##0.00) format.

Apply Format Cell Option and Keep Currency Format

  • Upon pressing ENTER, we will see that price in cell C5 has been formatted as currency in cell D5.

Custom Number Format

  • Now, we will drag the fill handle to apply the formula to the rest of the cells.

Apply Format Cell Option and Keep Currency Format

  • Now, we will see that each price value in the Price column has been formatted as currency in the Corrected Format.

Custom Number Format

Step 4:

  • Finally, we will concatenate the product name and the corrected format of the price. We will write down the below formula in cell E5.
=CONCATENATE(B5, " Price: ", D5)

Formula Breakdown:

The CONCATENATE formula will join or concatenate the cell values B5, “ Price:“ and D5.

CONCATENATE Formula in Excel

  • Upon pressing ENTER, we will get the concatenated text in cell E5.

Apply Format Cell Option and Keep Currency Format

  • Now, we will drag the fill handle to apply the formula to the rest of the cells.

Apply Format Cell Option and Keep Currency Format

  • Finally, we will see all the concatenated text with corrected currency format in the Correct Concatenation.

Apply Format Cell Option and Keep Currency Format

Read More: How to Combine Text from Two or More Cells into One Cell in Excel (5 Methods)


Method 2: Use the Ampersand (&) Symbol to Keep Currency Format

We can also keep the currency format while concatenating with the ampersand sign (&). Just follow the below steps.

Step 1:

  • Just like the above method, we will use the Format Cells to change the number format and then use the TEXT function to insert the prices under the Price column in the Corrected Format column with the new number format $#,##0.00_);($#,##0.00).

Custom Number Format

Step 2:

  • Now, we will concatenate the product name and the corrected format of the price. We will write down the below formula in cell E5.
=B5 & " Price: " & D5

Formula Breakdown:

The ampersand sign (&) will join or concatenate the cell values B5, “ Price:“ and D5.

Ampersand Sign (&) in Excel

  • Upon pressing ENTER, we will get the concatenated text in cell E5.

Use the Ampersand (&) Symbol to Keep Currency Format

  • Now, we will drag the fill handle to apply the formula to the rest of the cells.

Use the Ampersand (&) Symbol to Keep Currency Format

  • Finally, we will see all the concatenated text with corrected currency format in the Correct Concatenation.

Use the Ampersand (&) Symbol to Keep Currency Format

Read More: How to Concatenate Apostrophe in Excel (6 Easy Ways)


Similar Readings:


Method 3: Perform the TEXTJOIN Function to Keep Currency Format

If you have access to Microsoft Excel 365, you can also use the TEXTJOIN function to join or concatenate the text while keeping the currency format intact.

Step 1:

  • Just like the above method, we will use the Format Cells to change the number format and then use the TEXT function to insert the prices in the Corrected Format column with the new number format $#,##0.00_);($#,##0.00).

Custom Number Format

Step 2:

  • Now, we will concatenate the product name and the corrected format of the price. We will write down the below formula in cell E5.
=TEXTJOIN(" Price: ",TRUE,B5,D5)

Formula Breakdown:

The TEXTJOIN function will join or concatenate the cell values B5, “ Price:“ and D5.

TEXTJOIN function in Excel

Note: The TEXTJOIN function is an exclusive function only to be used in Microsoft Excel 365, Excel 2020, or Excel 2019. You should have access to any one of these to use the function.
  • Upon pressing ENTER, we will get the concatenated text in cell E5.

Perform the TEXTJOIN Function to Keep Currency Format

  • Now, we will drag the fill handle to apply the formula to the rest of the cells.

Perform the TEXTJOIN Function to Keep Currency Format

  • Finally, we will see all the concatenated text with corrected currency format in the Correct Concatenation.

Perform the TEXTJOIN Function to Keep Currency Format

Related Content: Combine Text in Excel (8 Suitable Ways)


Quick Notes

🎯 The CONCATENATE function is an earlier version of the CONCAT function. But both functions give the same result.

🎯  You should have access to  Microsoft Excel 365, Excel 2020, or Excel 2019 o use TEXTJOIN the function.


Conclusion

In this article, we have learned how to keep currency format in concatenate in Excel. I hope from now on you can keep currency format in concatenate in Excel very easily. However, if you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!


Related Articles

ASM Arman

ASM Arman

Hi there! I am ASM Arman. I Completed B.Sc. in Naval Architecture and Marine Engineering. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations. Have a great day!!!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo