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


Search for Tips


All Tips

Browse Tips by Category

Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks

© Copyright 2016, J-Walk & Associates, Inc.
Privacy Policy