How to Separate Bold and Non-Bold Text in an Excel Cell
Question details
The user needs to extract or separate text within a single cell based on whether the characters are formatted as bold or non-bold.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Cleaning data or extracting specific information where text formatting (boldness) is used to indicate different data categories within the same cell.
- Observed behavior
- Standard Excel formulas only evaluate the text value of a cell and cannot detect or extract partial text formatting like bold characters.
Ensure that the Developer tab is enabled in your ribbon and that your workbook is saved as a Macro-Enabled Workbook (.xlsm) so you can write and execute VBA code.
Use a VBA Macro to Extract Bold or Non-Bold Text
Create a custom User Defined Function (UDF) using VBA to inspect the Font.Bold property of individual characters within a cell.
Because built-in worksheet formulas cannot read partial cell formatting, VBA is required. A VBA script can loop through every character in a target cell, check if its Font.Bold property is True or False, and concatenate the matching characters into a new string.
Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.
In the VBA editor, click on 'Insert' in the top menu bar and select 'Module' to create a blank script window.
Paste a VBA function that loops through the cell's characters. For example, use a loop like 'For i = 1 To Len(Cell.Value)' and check 'If Cell.Characters(i, 1).Font.Bold = True Then' to build your bold text string.
Close the VBA editor. Return to your worksheet and type your new custom formula (e.g., =ExtractBold(A1)) into an empty cell to extract the bold text.
Use Macros to Manage Formatted Text in WPS Office
WPS Spreadsheet provides robust support for VBA macros, allowing you to easily run custom scripts to separate bold and non-bold text just like in Microsoft Excel.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your spreadsheet containing the mixed-format text.
- 2. Access the VBA Editor: Navigate to the 'Tools' tab on the ribbon and click on 'VBA Editor' to open the scripting environment.
- 3. Insert Your Macro: Click 'Insert' > 'Module' and paste your VBA code designed to separate bold and non-bold characters.
- 4. Use the Custom Formula: Go back to your spreadsheet and use your newly created custom function to extract the formatted text instantly.
Frequently Asked Questions
Can I separate bold text using standard Excel formulas?
No, standard Excel formulas (like LEFT, RIGHT, or MID) only read the text values within a cell. They cannot detect or evaluate text formatting such as bold, italic, or font color.
How do I save a workbook that contains this VBA code?
To keep your VBA macros intact, you must save your file as an Excel Macro-Enabled Workbook. Go to File > Save As, and choose '.xlsm' from the file format dropdown menu.
Can I modify the VBA code to extract text based on font color instead of bold formatting?
Yes. You can modify the VBA script to check the 'Font.Color' or 'Font.ColorIndex' property of the Characters object instead of the 'Font.Bold' property.
