Is A Particular Word Contained In A Text String?
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:
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
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
Needs tips? Here are two books, with nothing but tips:
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