How to Combine Excel Columns Without Preserving Mixed Formatting
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.

- 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.
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.
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.
Click on the cell where you want the combined text to appear, for example, cell D2.
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.
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 VBA Macro for Advanced Formatting Preservation
If preserving mixed character-level formatting is an absolute requirement, a VBA macro is necessary.
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. Open your workbook: Launch WPS Spreadsheet and open the document containing your separated data.
- 2. Apply TEXTJOIN formula: Select an empty cell and type =TEXTJOIN(" ", TRUE, B2, C2) to merge the values seamlessly.
- 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.

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.




