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

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.

dataset of excel concatenate keep text formatting

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.

cell formatting of excel concatenate keep text formatting

Steps:

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

cell formatting of excel concatenate keep text formatting

  • Press Enter.
  • You will find the result just like the picture given below.
  • 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

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

cell formatting of excel concatenate keep text formatting

  • Press Enter.
  • You will find the result in the picture given below.
  • Copy the formula from D5 to D10.

cell formatting of excel concatenate keep text formatting

  • 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


Method 2 – Inserting Ampersand Operator

Steps:

  • Select cell D5.

ampersand operator of excel concatenate keep text formatting

  • Enter the following formula in the cell:
=TEXT(C5,"$#,##0.00_);($#,##0.00)")

ampersand operator of excel concatenate keep text formatting

  • Press Enter.
  • You will get the result in the following picture.
  • Copy the formula from D5 to D10.

ampersand operator of excel concatenate keep text formatting

  • You will find the corrected format of the price column.

ampersand operator of excel concatenate keep text formatting

  • Select cell E5 and enter the following formula:
=B5 & " Price: " & D5

ampersand operator of excel concatenate keep text formatting

  • Press Enter to find the concatenation result in cell E5.

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


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.


Get FREE Advanced Excel Exercises with Solutions!
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