Is A Particular Word Contained In A Text String?

Category: Formulas / General VBA | [Item URL]

Here's a VBA function that might be useful in some situations.

The ExactWordInString functions returns True if a specified word is contained in a text string.

You might think that this function is just a variation on Excel's FIND function or VBA's Instr function. There's a subtle difference. The ExactWordInString function looks for a complete word -- not text that might be part of a different word.

The examples in the accompanying figure should clarify how this function works. Cell C2 contains this formula, which was copied to the cells below:

=ExactWordInString(A2,B2)

The function identifies the complete word trapped, but not the word trap, which is part of trapped. Also, note that a space is not required after a word in order to identify it as a word. For example, the word can be followed by a punctuation mark.

The function, listed below, modified the first argument (Text) and replaces all non-alpha characters with a space character. It then adds a leading and trailing space to both arguments. Finally, it uses the Instr function to determine if the modified Word argument is present in the modified Text argument.

To use this function in a formula, just copy and paste it to a VBA module in your workbook.

Function ExactWordInString(Text As String, Word As String) As Boolean
'   Returns TRUE if Word is contained in Text as an exact word match
    Dim i As Long
    Const Space As String = " "
    Text = UCase(Text)
'   Replace non-text characters with a space
    For i = 0 To 64
        Text = Replace(Text, Chr(i), Space)
    Next i
    For i = 91 To 255
        Text = Replace(Text, Chr(i), Space)
    Next i
'   Add initial and final space to Text & Word
    Text = Space & Text & Space
    Word = UCase(Space & Word & Space)
    ExactWordInString = InStr(Text, Word) <> 0
End Function

* Update *

Excel MVP Rick Rothstein sent me a much simpler function that produces the same result. In fact, it uses just one statement:

Function ExactWordInString(Text As String, Word As String) As Boolean
  ExactWordInString = " " & UCase(Text) & " " Like "*[!A-Z]" & UCase(Word) & "[!A-Z]*"
End Function