Coloring partial text within cell in Excel

There were two approaches I discovered to get around this problem, and neither where really optimal.

The first approach was breaking the strings into two separate columns, that way I could use one of the earlier described custom formatting to set its color. This isn't an ideal solution because I had to compromise on the "look and feel" of the report in order to accommodate that extra column.

The second approach is through using VBA/macros, which though I opted to avoid in this particular scenario, would have been up to the task. While I won't print out the entire working code, it essential boils down to this:

1. Find cell you wish to adjust (either through ActiveCell or a loop)
2. Use Instr function to find location in string where you wish to modify color
3. If text length is variable, use Instr again to find location in string where you wish to stop the color
4. Use the Characters(start, length) function to highlight the exact characters you want to modify, passing in the values found earlier.
5. Change the color with Font.Color = RGB(r,g,b)

An example using a macro can be found here:

Macro to colour part of the text in cells in Excel

Excel Macros - For Loop to Colour Part of Cells

Use an Excel macro that contains a for loop to loop through rows of weather data and colour part of the cell text red if it contains the word Hot and blue if it contains the word Cool:

Please note Hot and Cool are case sensitive for Instr.

Instructions

1. Click on the Developer tab in Excel
2. Click on the Visual Basic icon and copy the macro text below into 1the code window
3. Click the Excel icon to switch back to the Excel view
4. Click on the Macros icon, select the macro called TextPartColourMacro and click run

The Completed Macro:

Sub TextPartColourMacro()

' Declarations and Initialisation
Dim Row As Integer, Col As Integer
Dim CurrentCellText As String
Col = 1

' Loop Through Rows 2 to 5
For Row = 2 To 5

    ' Get Text in Current Cell
    CurrentCellText = ActiveSheet.Cells(Row, Col).Value
    
    ' Get the Position of the Text Hot and Cool
    HotStartPosition = InStr(1, CurrentCellText, "Hot")
    CoolStartPosition = InStr(1, CurrentCellText, "Cool")
    
    ' Colour the Word Hot Red
    If HotStartPosition > 0 Then
        ActiveSheet.Cells(Row, Col).Characters(HotStartPosition, 3).Font.Color = RGB(255, 0, 0)
    End If
    
    ' Colour the Word Cool Blue
    If CoolStartPosition > 0 Then
        ActiveSheet.Cells(Row, Col).Characters(CoolStartPosition, 4).Font.Color = RGB(0, 0, 255)
    End If

Next Row
End Sub