How to Make Text Bold After Specific Characters in Excel VBA
Question details
The user needs a method to automate the formatting of text within a range of cells, specifically applying a bold font style to all characters that appear immediately after a specific substring (e.g., 'MM').
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Formatting partial text strings across multiple rows in an Excel spreadsheet without manually editing each cell.
- Observed behavior
- Text inside the cells has uniform formatting. A VBA programmatic solution is required to isolate and bold only the portion of the string following a designated marker.
Ensure you are using a desktop version of Excel, as VBA macros are not supported in Excel for the Web. You should also verify that the Developer tab is enabled in your ribbon to access the Visual Basic Editor.
Use a VBA Macro to Format Partial Text
This method utilizes Excel's Visual Basic for Applications (VBA) to search for the specific characters ('MM') and apply bold formatting to the remainder of the cell's content.
By utilizing the InStr function in VBA, Excel can identify the exact starting position of your target string. We can then use the Characters object to apply the Bold property to everything following that position.
This macro is specifically written to check values starting from cell A2 downwards. You can adjust the Range values if your data is located in a different column.
Press Alt + F11 on your keyboard to launch the Visual Basic Editor in your desktop version of Excel.
From the top menu, click 'Insert' and select 'Module' to open a blank coding window.
Copy and paste the following code into the module window: Sub BoldAfterMM() Dim c As Range Dim p As Long Application.ScreenUpdating = False For Each c In Range(Cell1:=Range("A2"), Cell2:=Range("A2").End(xlDown)) p = InStr(c.Value, "MM") If p > 0 Then c.Characters(Start:=p + 2, Length:=1000).Font.Bold = True End If Next c Application.ScreenUpdating = True End Sub
Place your text cursor anywhere inside the code and press F5 on your keyboard to execute the macro and format your text.
To keep the macro for future use, click 'File' > 'Save As' and choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the file format dropdown menu.
Run VBA Macros Seamlessly in WPS Office
WPS Office fully supports VBA macros, allowing you to run the exact same script to apply bold formatting to specific characters. It provides an efficient and highly compatible spreadsheet environment for all your automation needs.
- 1. Open Your Spreadsheet in WPS: Launch WPS Spreadsheet and open the document containing the data you want to format.
- 2. Access the VBA Editor: Navigate to the 'Developer' tab on the top ribbon and click 'VB Editor', or simply press Alt + F11.
- 3. Insert and Run the Code: Click 'Insert' > 'Module', paste the VBA code provided in the solution above, and press F5 to execute the formatting script.
Frequently Asked Questions
Why isn't the macro working in Excel for the Web?
VBA macros rely on the desktop architecture of Excel. Excel for the Web does not support the execution of VBA scripts. You must open your file in the desktop application to run the macro.
How do I apply this macro to a different column?
In the VBA code, locate the line `Range(Cell1:=Range("A2"), Cell2:=Range("A2").End(xlDown))`. Change both instances of 'A2' to the starting cell of your desired column, such as 'B2' or 'C2'.
Will this macro override my existing cell formatting?
The macro explicitly targets the Characters object's Font.Bold property for the text following the target string. It will leave the font color, size, and other attributes intact, but the specific targeted substring will become bold.
Do I have to change the file format after adding a macro?
Yes. Standard Excel workbooks (.xlsx) cannot store VBA code. You must save the document as an Excel Macro-Enabled Workbook (.xlsm) to ensure your script is saved for future sessions.
