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.

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:

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

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

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

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

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