0

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.

Sudhanshu Gupta
  • 1
  • 1
  • 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 Dec 08 '20 at 08:20
  • 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 Dec 08 '20 at 08:25
  • 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 Dec 08 '20 at 08:27

1 Answers1

2

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

sbgib
  • 5,580
  • 3
  • 19
  • 26
  • 1
    `\b` will do just fine, see [this](https://regex101.com/r/FuMIk4/2). The linked answer in your explaination also shows that there is no difference and you can drop the ancors. – JvdV Dec 08 '20 at 08:33
  • Hi, it may behave differently on different versions of Excel. Please follow the link for more information. – sbgib Dec 08 '20 at 08:35
  • @sbgib That answer is not correct. `\bThis` will always match `This` in `This is it`, just tested in VBA. – Wiktor Stribiżew Dec 08 '20 at 11:27
  • 1
    @WiktorStribiżew, done. The original question originates from a typo I'm sure. – JvdV Dec 08 '20 at 11:32
  • Ok, I've simplified the answer. I have no preference about it, I simply tried to make sure it worked for the OP with an unknown Excel version. – sbgib Dec 09 '20 at 07:27