Formatting Issues

How to Combine Excel Columns Without Preserving Mixed Formatting

WPS EditorWPS Editor Oct 7, 2026 871 views

Question details

Combine text from multiple columns into a single column using a formula, understanding that original font colors and conditional formatting cannot be retained.

How to Combine Excel Columns Without Preserving Mixed Formatting
Product
Excel
Device & OS
not provided
Scenario
Merging data from two or more columns into one new column for consolidated reporting or data organization.
Observed behavior
Formulas combine the text perfectly but strip away any original character-level formatting, conditional formatting, and font colors from the source cells.
Before you start

Verify if the visual formatting (like specific text colors or conditional rules) is strictly necessary in the combined column. If it is, you will need to reapply these rules manually after using formulas.

Solution 1Recommended

Use the TEXTJOIN Formula to Combine Data

The TEXTJOIN function efficiently merges text from multiple cells, allowing you to specify a delimiter and ignore empty cells.

Standard Excel formulas only extract the underlying data values, not the visual styling. Any character-level font colors or conditional formatting from the source columns will not carry over to the new cell.

1
Select the target cell

Click on the cell where you want the combined text to appear, for example, cell D2.

2
Enter the TEXTJOIN formula

Type the formula =TEXTJOIN(" ", TRUE, B2, C2) and press Enter. This joins the contents of B2 and C2 separated by a space, while ignoring any empty cells.

3
Fill down the column

Click and drag the fill handle at the bottom right of cell D2 down the column to apply the formula to the remaining rows.

Use the TEXTJOIN Formula to Combine Data
Reapplying Formatting: Because the formula does not preserve the original font or conditional formatting, you must manually recreate or copy the applicable formatting rules for the new combined column.
Efficient Data Processing in WPS Office

Easily Combine Data and Manage Formatting with WPS Spreadsheet

WPS Spreadsheet provides powerful functions like TEXTJOIN and intuitive formatting tools to help you seamlessly merge columns and quickly reapply custom styles to your data.

  1. 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your separated data.
  2. 2. Apply TEXTJOIN formula: Select an empty cell and type =TEXTJOIN(" ", TRUE, B2, C2) to merge the values seamlessly.
  3. 3. Reapply conditional formatting: Highlight the newly combined column, navigate to the Home tab, and select 'Conditional Formatting' to apply new styling rules to the merged text.
100% compatible with Microsoft Excel formulas, ensuring TEXTJOIN works flawlessly.Lightweight and fast performance when processing large datasets.Intuitive Conditional Formatting manager to quickly establish new visual rules on combined columns.
microsoft office alternative - wps office

Frequently Asked Questions

Can the CONCATENATE function preserve font colors?

No. Like TEXTJOIN, CONCATENATE (or the ampersand operator) only retrieves the text values from the source cells. It cannot pull the rich text formatting or font colors into the new cell.

How do I keep conditional formatting when combining cells?

Conditional formatting cannot be carried over directly through a formula because a single cell cannot apply different conditional formats to separate text portions within itself. You must create a new conditional formatting rule specifically for the combined column.

Why does my VBA code for combining formatted text throw a type mismatch error?

A type mismatch error usually occurs if the VBA code assumes every cell contains compatible font information or if it calculates incorrect character lengths. Ensure you validate the character ranges for both source cells and declare your worksheet variables correctly.