ASM Arman
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.
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.
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.
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.
- Upon pressing ENTER, we will see that price in cell C5 has been formatted as currency in cell D5.
- Now, we will drag the fill handle to apply the formula to the rest of the cells.
- Now, we will see that each price value in the Price column has been formatted as currency in the Corrected 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.
- Upon pressing ENTER, we will get the concatenated text in cell E5.
- Now, we will drag the fill handle to apply the formula to the rest of the cells.
- Finally, we will see all the concatenated text with corrected currency format in the Correct Concatenation.
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).
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: " & D5Formula Breakdown:
The ampersand sign (&) will join or concatenate the cell values B5, “ Price:“ and D5.
- Upon pressing ENTER, we will get the concatenated text in cell E5.
- Now, we will drag the fill handle to apply the formula to the rest of the cells.
- Finally, we will see all the concatenated text with corrected currency format in the Correct Concatenation.
Read More: How to Concatenate Apostrophe in Excel (6 Easy Ways)
Similar Readings
- Concatenate Numbers in Excel (4 Quick Formulas)
- VBA to Concatenate Range with Separator in Excel (3 Ways)
- Concatenate Not Working in Excel (3 Reasons with Solutions)
- How to Concatenate Columns in Excel (8 Simple Methods)
- Concatenate Rows in Excel (11 Methods)
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).
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.
- Upon pressing ENTER, we will get the concatenated text in cell E5.
- Now, we will drag the fill handle to apply the formula to the rest of the cells.
- Finally, we will see all the concatenated text with corrected currency format in the Correct Concatenation.
Read More: [Solved]: Currency Format Not Working in Excel
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!!!
