We have a dataset called Product Price of ABC Traders. The dataset has two columns, B and C, called Product and Price. The Product column refers to the product names, and the Price column refers to the price of the products. The text format of the C column is in currency format. The dataset ranges from B4 to C10.
Read More: How to Concatenate with Delimiter in Excel
Method 1 – Using Cell Formatting
We have added two new columns in the dataset, D & E, called corrected format and Concatenation.
Steps:
- Select cell D5.
- Enter the following formula in the selected cell:
=TEXT(C5,"$#,##0.00_);($#,##0.00)")
- Press Enter.
- You will find the result just like the picture given below.
- Fill Handle the formula down from D5 to D10.
- As a result, you will find the corrected format of the price cells.
- Copy the following formula in cell E5:
=CONCATENATE(B5, " Price: ", D5)
- Press Enter.
- You will find the result in the picture given below.
- Copy the formula from D5 to D10.
- You will find the result in the picture given below.
Read More: How to Concatenate with Space in Excel
Method 2 – Inserting Ampersand Operator
Steps:
- Select cell D5.
- Enter the following formula in the cell:
=TEXT(C5,"$#,##0.00_);($#,##0.00)")
- Press Enter.
- You will get the result in the following picture.
- Copy the formula from D5 to D10.
- You will find the corrected format of the price column.
- Select cell E5 and enter the following formula:
=B5 & " Price: " & D5
- Press Enter to find the concatenation result in cell E5.
- After copying down the formula, you will get the desired result in the E column.
Read More: How to Concatenate Apostrophe in Excel
Method 3 – Use the TEXTJOIN Function
Steps:
- I have included the corrected price format in this dataset.
- Enter the following formula in cell E5:
=TEXTJOIN(" Price: ",TRUE,B5,D5)
- Press Enter.
- You will get the concatenated value.
- Copy down the formula from E5 to E10 cell.
- The concatenated format will appear in the last column.
Read More: How to Bold Text in Concatenate Formula in Excel
Things to Remember
- Please remember that the TEXTJOIN function can be used only in Excel 2019, 2020, and 365.
Download the Practice Workbook
Please download the workbook to practice.
Related Articles
<< Go Back to Concatenate Excel | Learn Excel
