Find word (but not inside another word) in a text w/ VBA

0
votes

For example, let's say I have the following word and texts:

word  = "bee"  
text1 = "bla bla bee  bla bla... "  
text2 = "bla bla beep bla bla ..."

I want to return True for the first case and False for the second one.

After searching for a while, I've found a way to find a word in a text by using this:

If Application.IsNumber(Application.Search(word, text1)) Then 'returns True
If Application.IsNumber(Application.Search(word, text2)) Then 'returns True

But it returns True for both cases.
Is there an easy way (which would work with any word and text) to check if a text contains exactly the word that i'm looking for, but not inside other words?

2
stringvba
Sure. Check the results from your above code for whitespace or punctuation at both ends. – nicomp
That is a great idea @nicomp! But if the word is the first or the last one in a text, is there any way to find it as well? – Kobayashi
Sure there is. No problem with the boundary conditions. – nicomp

2 Answers

0
votes

Add spaces to the front and back to each input, also no need for worksheet function when vba has Instr:

Sub test()
Dim text1 As String
Dim word As String

word = "bee"
text1 = "bla bla beep bla bla... "


If InStr(" " & text1 & " ", " " & word & " ") > 0 Then
    MsgBox "True"
Else
    MsgBox "False"
End If
End Sub
0
votes

My first thought was to use a Regex (aka Regular Expression's). Testing with your words gave the results requested. The \b that's prefixed and suffixed indicate that those characters are boundary anchors, they start and end the word. The word is literally bee and is case sensitive. You'd use regex.IgnoreCase = True to make it case insensitive.

Sub Testing()
    Dim word As String
    word = "bee"

    Dim isFound As String
    isFound = "bla bla bee  bla bla... "

    Dim isNotFound As String
    isNotFound = "bla bla beep bla bla ..."

    Debug.Print IsWordFound(word, isFound)
    Debug.Print IsWordFound(word, isNotFound)
End Sub

Public Function IsWordFound(ByVal wordToFind As String, ByVal textToSearch As String) As Boolean
    'Requires Tools>References>Microsoft VBScript Regular Expressions 5.5 to be checked
    Dim regex As VBScript_RegExp_55.RegExp
    Set regex = New RegExp
    regex.Pattern = "\b" & wordToFind & "\b"
    IsWordFound = regex.Test(textToSearch)
End Function