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.

You can also just use Like operator, e.g.: Debug.Print str Like "* " & search Or str Like search & " *" Or str Like "* " & search & " *" where str is the string to be evaluated. — JvdV
I tried this Debug.Print (text Like "* " & search & " *") it is failing for search phrases that are at the beginning or at the end of the text to be evaluated. — Sudhanshu Gupta
That depends on what you used as text variable. If I was you I would concatenate a leading and trailing space while declaring the variable. In such case the Like will perform just as expected. That means you can also get rid of all the Or statements. — JvdV

sbgib sbgib · Accepted Answer · 2020-12-08T08:27:20

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).

Regular expression in Excel VBA to perform exact match

1 Answers