How to Retain Cell Formatting When Combining Text in Excel
Question details
The user wants to preserve specific cell formatting (such as partial italics) when combining text from multiple cells into a single destination cell.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Attempting to concatenate text from multiple source cells using built-in formulas like CONCAT or TEXTJOIN.
- Observed behavior
- Native Excel formulas return plain text, stripping away any mixed formatting such as bold or italics applied to specific characters within the source cells.
Since standard spreadsheet functions cannot return richly formatted text, prepare to use Visual Basic for Applications (VBA) to solve this issue. Ensure you have saved a backup of your workbook in a macro-enabled format (.xlsm) before running custom scripts.
Combine Cells and Retain Formatting via VBA
A VBA macro allows you to combine text strings and systematically apply the exact character formatting from the source cells to the new combined cell.
Because Excel functions evaluate values rather than formats, you cannot use CONCAT or TEXTJOIN to keep partial formatting. VBA solves this limitation by reading the text, pasting the combined string into the destination, and then mapping the formatting character-by-character using the Characters object.
Press Alt + F11 on your keyboard to open the Visual Basic for Applications Editor.
Click 'Insert' in the top menu and select 'Module' to create a blank workspace for your code.
Paste a custom VBA script designed to loop through your target cells, combine their text values, and copy the .Characters.Font properties from the source to the destination cell.
Close the VBA editor, return to your sheet, and press Alt + F8. Select your new macro from the list and click 'Run'.
Use Separate Adjacent Cells Instead of Combining
If using VBA is too complex for your current workflow, you can visually combine data by strategically hiding borders between adjacent cells.
Generate HTML Output to Retain Formatting
Advanced users working with databases can generate an HTML string using an SQL query and render the formatted text.
Manage Advanced Formatting with WPS Spreadsheet
WPS Spreadsheet offers robust support for VBA macros, allowing you to easily run custom scripts that combine text while preserving rich formatting like italics and bold text. It is highly compatible with macro-enabled files and provides a smooth interface for complex data tasks.
- 1. Open Your Data in WPS Office: Launch WPS Spreadsheet and open the file containing the text you wish to combine.
- 2. Access Developer Tools: Navigate to the 'Developer' tab on the top ribbon.
- 3. Launch the VBA Editor: Click 'Visual Basic Editor' to input your advanced text combination and formatting script.
- 4. Execute the Code: Run the macro to instantly combine your chosen cells while perfectly preserving any specific character formatting.
Frequently Asked Questions
Why do formulas like CONCAT and TEXTJOIN lose my italic formatting?
Excel functions are designed to extract and calculate only the underlying raw data (values) from cells. They do not extract or process the metadata associated with rich text formatting, such as bold, italics, or font colors.
Can I use conditional formatting to apply italics to part of a cell's text?
No, conditional formatting applies rules to the entire cell. To format only specific characters within a single cell, you must apply the formatting manually from the Home tab or utilize a VBA macro.
Is there a native feature to merge cells and keep the text from all cells?
When you use the native 'Merge Cells' button in Excel or WPS, it only keeps the value of the upper-left cell and discards the rest. To combine the text of all cells, you must use concatenation formulas or macros.
