How to Split Bold and Non-Bold Text into Excel Columns
Question details
The user needs a method to separate text located inside a single cell into different columns depending on whether specific characters are formatted as bold or non-bold.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Sorting or extracting mixed-format text data where bolding has been used to indicate a distinct data category, such as extracting names (bold) from addresses (non-bold) within the same cell.
- Observed behavior
- Excel's native Text-to-Columns feature and standard formulas cannot recognize character-level bold formatting, leaving users unable to split the text automatically.
Ensure that the Developer tab is enabled in your Excel ribbon, as you will need to open the Visual Basic Editor to create a custom User-Defined Function (UDF) that reads character font properties.
Create a Custom VBA Macro to Extract Bold Text
Use VBA to create a custom function that loops through each character in a cell, inspects its Font.Bold property, and extracts the bold or non-bold text into distinct strings.
Since standard Excel formulas only read cell values and ignore formatting, you must use Visual Basic for Applications (VBA) to evaluate the formatting properties of individual characters within a cell string.
Go to File > Options > Customize Ribbon. In the right pane, check the box for 'Developer' and click OK.
Navigate to the Developer tab and click 'Visual Basic', or press Alt + F11 on your keyboard to open the VBA Editor.
In the VBA Editor, right-click on your workbook name in the Project Explorer panel, select Insert, and then click 'Module'.
Paste a VBA script that defines a new function (e.g., Function GetBoldText). The script should contain a loop that checks ActiveCell.Characters(i, 1).Font.Bold and appends the character to the result if true.
Return to your Excel sheet. In an adjacent blank column, type your new custom function (e.g., =GetBoldText(A1)) and press Enter to extract the bold text.
Go to File > Save As, and change the file type to 'Excel Macro-Enabled Workbook (*.xlsm)' so your VBA code is not lost when you close the file.
Run VBA Macros Seamlessly in WPS Office
WPS Spreadsheet fully supports VBA macros and custom user-defined functions, allowing you to easily run scripts to split text by bold formatting without needing a costly Microsoft Excel subscription.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your dataset containing the mixed-format text.
- 2. Access the VBA Editor: Navigate to the Developer tab on the ribbon and click on 'VBA Editor'.
- 3. Insert the extraction macro: Right-click in the Project window, select Insert > Module, and paste your bold-text extraction VBA code.
- 4. Extract the text: Return to your spreadsheet and type your custom formula (e.g., =ExtractBold(A1)) to perfectly split the bold and non-bold text into separate columns.
Frequently Asked Questions
Can I split bold text using standard Excel formulas instead of VBA?
No. Standard Excel functions like LEFT, RIGHT, MID, or the Text-to-Columns wizard only read the underlying plain text values. They cannot detect or interact with character-level formatting such as bold, italics, or font color. A custom VBA function is strictly required.
Why does my extracted bold text not update automatically when I change the formatting?
Excel's calculation engine is triggered by changes in cell values, not changes in cell formatting. If you make an existing word bold after applying the VBA formula, you must force Excel to recalculate by pressing F9 or double-clicking the cell with the formula and pressing Enter.
How do I save my workbook after adding the bold text extraction macro?
You must save the file as an Excel Macro-Enabled Workbook (.xlsm). If you attempt to save it as a standard Excel Workbook (.xlsx), Excel will display a warning and subsequently strip the VBA code from the file, breaking your custom formula.
