Regular expression in Excel VBA to perform exact match

Question

So, I am taking the text from a Cell in excel and then searching for a particular phrase in that text. Now, I need to return true only if it is an exact word match. For e.g. if the text is "The Quick brown fox jumped over the lazy dog" and I am searching for "Qui" it should return false, but if I search for "Quick", it should return true. Similarly, "brown Quick" should return false and "Quick brown" should return true.

So, I tried this using regular expressions in VBA and came up with

 With regex
    .Pattern = " *" & search & " *"
 End With

Using this code I am not able to generate the desired output. I need to check if the character before the search phrase is either a space or nothing and the character at the end of the search phrase is also either a space or nothing. Kindly help me with it.

Answer 1

Try like this:

Public Function TestRegex(str As String)
    Dim RegEx As Object
    Set RegEx = CreateObject("VBScript.RegExp")
    
    With RegEx
        .Pattern = "\b" & str & "\b"
        .Global = True
    End With
    
    TestRegex = RegEx.Test("The Quick brown fox jumped over the lazy dog")
End Function

Results:

?TestRegex("Qui")
False

?TestRegex("Quick")
True

?TestRegex("brown Quick")
False

?TestRegex("Quick brown")
True

By using word boundary (\b), you can specify in the regex pattern that you are searching for complete word(s).