In Excel, you can join the texts by using the CONCATENATE function. But, the problem arises when you need to keep the same text format after using the CONCATENATE function. In this article, I will show you how to concatenate cells but keep text formatting in Excel. I will show three methods here. I hope the methods will help you to increase your Excel skills. So, without any further delay, let’s jump into the solutions.
Download Practice Workbook
Please download the workbook to practice yourself.
3 Effective Ways to Concatenate Cells but Keep Text Formatting in Excel
Here, I will consider a dataset called Product Price of ABC Traders. The dataset has two columns, B & 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.
1. Using Cell Formatting
This is the first method of this article, I will use cell formatting here to how to concatenate cells but keep text formatting in Excel. I will describe the method step by step. I have added two new columns in the dataset, D & E, called corrected format and Concatenation. For your better understanding, I added relevant illustrations with the steps.
Steps:
- Select the D5 cell first.
- write down the following formula in the selected cell.
=TEXT(C5,"$#,##0.00_);($#,##0.00)")
- After that, press the Enter key.
- As a consequence, you will find the result just like the picture given below.
- Now, Fill-handle the formula down from D5 to D10.
- As a result, you will find the corrected format of the price cells.
- Now, copy the following formula in the E5Â cell.
=CONCATENATE(B5, " Price: ", D5)
- Now, press the Enter key.
- You will find the result like the picture given below.
- Then, copy down the formula from D5 to D10.
- Consequently, you will find the result like the picture given below.
Read More: How to Combine Text from Two or More Cells into One Cell in Excel (5 Methods)
Similar Readings
- How to Concatenate Two Columns In Excel (5 Methods)
- Concatenate Multiple Cells Based on Criteria in Excel (4 Methods)
- How to Concatenate Columns in Excel (8 Simple Methods)
- Concatenate Numbers in Excel (4 Quick Formulas)
- How to Concatenate String and Integer using VBA
2. Inserting Ampersand Operator
This is the second method of this article. I will use the Ampersand operator to how to concatenate cells but keep text formatting in Excel. Please follow the steps given below.
Steps:
- First, select the D5Â cell.
- Then, write down the following formula in the cell.
=TEXT(C5,"$#,##0.00_);($#,##0.00)")
- Press the Enter button then.
- After that, you will get the result like the following picture.
- Then, copy down the formula from D5 to D10.
- Hence, you will find the corrected format of the price column.
- Moreover, select the E5 cell and write down the following formula.
=B5 & " Price: " & D5
- After pressing Enter, you will find the concatenation result in the E5Â cell.
- After copying down the formula, you will get the desired result in the EÂ column.
Read More: Combine Multiple Columns into One Column in Excel
3. Use of TEXTJOIN Function
This is the last method among all three methods I need to describe. Here I will use the TEXTJOIN function to how to concatenate cells but keep text formatting in Excel. So, let’s follow the steps by step procedure.
Steps:
- I have included the corrected format of price in this dataset.
- Then, write down the following formula in the E5Â cell.
=TEXTJOIN(" Price: ",TRUE,B5,D5)
- Hence, press the Enter key.
- After that, you will get the concatenated value.
- Then, copy down the formula from E5 to E10Â cell.
- As a result, the concatenated format will appear in the last column.
Read More: How to Concatenate in Excel (3 Suitable Ways)
Things to Remember
- You need to bear in mind that, the TEXTJOIN function can be used only in EXCEL 2019, EXCEL 2020, and Excel 365.
Conclusion
In this article, I have tried to explain, how to concatenate cells but keep text formatting in Excel. I hope, you have learned something new from this article. Now, extend your skill by following the steps of these methods. You will find such interesting blogs on our website Exceldemy.com. I hope you have enjoyed the whole tutorial. If you have any kind of queries feel free to ask me in the comment section. Don’t forget to give us your feedback.
Related Articles
- How to Concatenate Names in Excel (5 Easy Ways)
- Concatenate and Keep Number Format in Excel
- How to Concatenate Date/Day, Month, and Year in Excel (5 Methods)
- Concatenate Multiple Cells with Comma in Excel (4 Ways)
- Macro to Concatenate Multiple Columns in Excel (with UDF and UserForm)
- How to Combine Rows into One Cell in Excel
- Opposite of Concatenate in Excel (4 Options)