How to Concatenate Cells but Keep Text Formatting in Excel (3 Effective Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Concatenate Cells but Keep Text Formatting in Excel: 3 Effective Ways

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.

dataset of excel concatenate keep text formatting

Read More: How to Concatenate with Delimiter in Excel


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.

cell formatting of excel concatenate keep text formatting

Steps:

  • Select the D5 cell first.
  • write down the following formula in the selected cell.
=TEXT(C5,"$#,##0.00_);($#,##0.00)")

cell formatting of excel concatenate keep text formatting

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

cell formatting of excel concatenate keep text formatting

  • As a result, you will find the corrected format of the price cells.

cell formatting of excel concatenate keep text formatting

  • Now, copy the following formula in the E5 cell.
=CONCATENATE(B5, " Price: ", D5)

cell formatting of excel concatenate keep text formatting

  • Now, press the Enter key.
  • You will find the result in the picture given below.
  • Then, copy down the formula from D5 to D10.

cell formatting of excel concatenate keep text formatting

  • Consequently, you will find the result in the picture given below.

cell formatting of excel concatenate keep text formatting

Read More: How to Concatenate with Space in Excel


2. Inserting Ampersand Operator

This is the second method of this article. I will use the Ampersand operator to concatenate cells but keep text formatting in Excel. Please follow the steps given below.

Steps:

  • First, select the D5 cell.

ampersand operator of excel concatenate keep text formatting

  • Then, write down the following formula in the cell.
=TEXT(C5,"$#,##0.00_);($#,##0.00)")

ampersand operator of excel concatenate keep text formatting

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

ampersand operator of excel concatenate keep text formatting

  • Hence, you will find the corrected format of the price column.

ampersand operator of excel concatenate keep text formatting

  • Moreover, select the E5 cell and write down the following formula.
=B5 & " Price: " & D5

ampersand operator of excel concatenate keep text formatting

  • After pressing Enter, you will find the concatenation result in the E5 cell.

ampersand operator of excel concatenate keep text formatting

  • After copying down the formula, you will get the desired result in the column.

Read More: How to Concatenate Apostrophe 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 concatenate cells but keep text formatting in Excel. So, let’s follow the step-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 Bold Text in Concatenate Formula in Excel


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.

Download Practice Workbook

Please download the workbook to practice yourself.


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


What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Souptik Roy
Souptik Roy

Souptik Roy, a BSc graduate in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology, dedicated 1.5 years to the ExcelDemy project. During this time, he authored 50+ articles and reviewed 20+ for ExcelDemy. Presently, he is a designer and content developer at YouHaveGotThisMath and Brainor, sister concerns of ExcelDemy. His educational content spans science, mathematics, and grammar. Roy's interests include developing creative ideas, visualizing concepts with tools like Adobe Illustrator, and problem-solving within Microsoft... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo