VBA & Macro Problems

How to Format Part of a Text String in an Excel Cell Using VBA

Aamir Naveed Akram Oct 9, 2026 869 views

Question details

The user needs to apply rich-text formatting, such as font color or size, to a specific substring or pattern within a text string across multiple cells using VBA.

How to Format Part of a Text String in an Excel Cell with VBA

Product: Microsoft Excel
Device & OS: not provided
Scenario: Formatting specific variable values that appear after a repeated label (like "TRIP:") within long text strings throughout a worksheet.
Observed behavior: Applying default cell formatting modifies the entire cell's text. Dynamically targeting and formatting only a partial string based on text patterns requires a VBA macro.

Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and test any VBA script on a backup copy of your worksheet to prevent unintended formatting changes.

Solution 1Recommended

Format Substrings Using the VBA Range.Characters Property

Use a VBA macro to loop through your used range, locate the target word with the InStr function, and apply formatting to specific characters.

The Range.Characters property allows you to modify the font attributes (such as color, size, and boldness) of a specific segment of text within a cell without affecting the rest of the string.

Open the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor in Excel.

Insert a New Module

Click on Insert in the top menu and select Module to create a blank module for your macro.

Write the Formatting Macro

Paste a macro using a loop and the InStr function. For example, to find "TRIP" and color the text that follows it red, use: Dim cell As Range For Each cell In ActiveSheet.UsedRange If InStr(cell.Value, "TRIP") > 0 Then cell.Characters(InStr(cell.Value, "TRIP") + 5, Len(cell.Value) - InStr(cell.Value, "TRIP") - 3).Font.Color = vbRed End If Next cell

Adjust the Starting Position and Length

Modify the math within the cell.Characters(start, length) parameters. The '+ 5' offset skips the word 'TRIP:', allowing you to target just the numbers or text that follow it.

Run the Macro

Press F5 or click the Run button on the toolbar to execute the script and apply the formatting across the active worksheet.

Format Substrings Using the VBA Range.Characters Property

Understanding the InStr Function: The InStr function returns the exact numerical starting position of your target text pattern. Combining this with string length (Len) helps isolate dynamic variables that vary in length.

Advanced Macro Support in WPS Office

Use VBA to Format Cell Strings in WPS Spreadsheet

WPS Spreadsheet features robust, built-in support for VBA and macros, allowing you to run custom scripts like the Range.Characters method to automate partial text formatting effortlessly.

1. Open Your Workbook in WPS Spreadsheet: Launch WPS Office, open your spreadsheet file, and navigate to the Tools tab on the ribbon.
2. Access the Visual Basic Editor: Click on the Macro button and select Visual Basic Editor from the dropdown menu, or simply press ALT + F11.
3. Run the Text Formatting Script: Insert a new module, paste your VBA code utilizing the Range.Characters property, and press F5 to format your specific substrings instantly.

Full compatibility with Microsoft Excel VBA macros and scriptsNative support for .xlsm macro-enabled workbook formatsLightweight application with fast data processing performanceFree alternative with a highly familiar ribbon interface

Frequently Asked Questions

Can I format multiple different parts of a text string in the same Excel cell?

Yes. You can use multiple Range.Characters statements within your VBA loop, utilizing different InStr searches to apply various formatting styles to multiple substrings inside a single cell.

Why does my VBA code overwrite the existing formatting in the cell?

If your macro modifies the entire cell value (e.g., cell.Value = ...) rather than specifically targeting the .Characters(start, length).Font property, Excel will reset any existing rich-text formatting. Ensure you only interact with the characters property.

Can I apply partial text formatting to cells containing formulas?

No, partial rich-text formatting—whether done manually or via VBA—only works on cells that contain static text strings. If a cell contains a formula, you cannot format just a portion of the output string.

Excel & Data Problems