Can Excel CONCATENATE Make Part of a Result Bold?
Question details
The user wants to know if they can apply bold formatting to specific parts of a text string generated by a CONCATENATE formula.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Combining text strings from multiple cells using formulas, while needing specific portions (such as particular words or variable results) to stand out with bold formatting.
- Observed behavior
- Excel formulas return plain text and natively cannot apply rich-text formatting to only selected portions of the formula result.
Because spreadsheet formulas inherently return unformatted plain text, you must convert your CONCATENATE formula results into static text values before you can apply bold formatting to specific words.
Convert Formula to Values and Format Manually
The simplest and most reliable workaround is to convert the formula result into a static text string, which then allows you to manually bold the specific sections.
Formulas in Excel update dynamically, which prevents partial cell formatting. By converting the formula to a static value, you unlock the ability to edit and format individual characters within the cell.
Select the cell containing your CONCATENATE formula, right-click it, and select 'Copy' (or press Ctrl+C).
Right-click the same cell (or a new destination cell) and choose 'Paste as Values' (the clipboard icon with the 123 symbol) to replace the formula with static text.
Double-click the cell to enter Edit mode. Highlight the specific text or words you want to emphasize, and click the 'Bold' icon on the Home tab (or press Ctrl+B).
Use a VBA Macro for Automated Rich-Text Formatting
If you have many cells to process and need specific parts of the combined text to be bold automatically, you can use a VBA macro to handle the concatenation and formatting simultaneously.
Easily Combine and Format Text in WPS Spreadsheet
Use WPS Spreadsheet to seamlessly combine text data and apply rich-text formatting. With its intuitive interface and high performance, you can quickly manage complex datasets, convert formulas to values, and format specific words without hassle.
- 1. Open your data: Launch WPS Spreadsheet and open your workbook containing the text data.
- 2. Combine text: Use the CONCATENATE or TEXTJOIN function to merge your desired strings.
- 3. Convert to values: Copy the result cell and select 'Paste as Values' from the right-click menu.
- 4. Format specific text: Double-click the cell, highlight the portion you want to bold, and press Ctrl+B.
Frequently Asked Questions
Can conditional formatting make part of a cell bold based on a formula?
No, conditional formatting applies the specified style to the entire cell. You cannot use conditional formatting to target and bold only specific words within a text string.
Is there any Excel add-in that allows partial formatting inside formulas?
Because the limitation lies within the core Excel formula engine returning plain text, no add-in can make a live formula display partial rich text. Add-ins that achieve this actually convert the formula to static text in the background before applying formatting.
Does the newer TEXTJOIN function support partial bold formatting?
No. Just like CONCATENATE, TEXTJOIN and the ampersand (&) operator both return standard plain text strings. You still need to convert the result to values to apply partial bold formatting.
How do I combine text with line breaks in a formula?
You can use the CHAR(10) function within your CONCATENATE or TEXTJOIN formula to insert a line break. For the line break to display properly, ensure that 'Wrap Text' is enabled on the destination cell.
