excel - How to Bold certain text part of a string

Question

I have a string within a cell and I am trying to bold certain parts of that string. I have my code setup so each case is a line within that cell.

The first cell is what I am starting out with, and the one below it is what I am trying to do. Below is my code on what I have so far.

Sub test()

            For Each cel In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
             
                 Dim arr, line As Long, pos As Long, txt, length, dashPos
                 
                 arr = Split(cel.Value, Chr(10)) ' Spliting cell contents by newline character
                 
                 pos = 1
                 For line = 1 To UBound(arr) + 1
                     
                     txt = arr(line - 1)
                     length = Len(txt)
                 
                     'check which line we're on...
                     Select Case line
                         Case 4: 'Underline on line 4
                             cel.Characters(pos, length).Font.Underline = True
                         Case 5: 'Bold the team players
                            
                            
                         Case 6: 'Underline on line 6
                             cel.Characters(pos, length).Font.Underline = True
                     End Select
                 
                     pos = pos + Len(txt) + 1 'start position for next line
                 Next line
    
                Next cel
End Sub

question from:https://stackoverflow.com/questions/65912765/how-to-bold-certain-text-part-of-a-string

Answer 1

Since you are looking up a certain pattern I thought this could be done through regular expressions since each match in the MatchCollection2 object will have a starting index including the length of the captured pattern. Let's imagine the following sample data:

Now we can apply the following code:

Sub Test()

Dim str As String: str = [A1]
Dim colMatch, objMatch

With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = "S+:(?=.*$)"
    If .Test(str) = True Then
        Set colMatch = .Execute(str)
        For Each objMatch In colMatch
            Range("A1").Characters(objMatch.FirstIndex, objMatch.Length).Font.Bold = True
        Next
    End If
End With

End Sub

The result:

---

About the regular expression's pattern:

S+:(?=.*$)

You can see an online demo here and a small breakdown below:

• S+: - 1+ Non-whitespace character up to and including a colon.
• (?= - A positive lookahead:
  • .*$ - 0+ characters other than newline up to the end string anchor.
  • ) - Close positive lookahead.

Note: We need to either forget about the "Newline" property of the regex object or set it's value to FALSE. In the example I gave I simply didn't include it because it will then default to FALSE. If this was set to true the end string anchor won't simply match the end of the whole string but the end of each line (which is what we want to avoid if we don't want to match "Server:").