Author: Oscar Cronquist Article last updated on February 26, 2018

I read an interesting blog post Is A Particular Word Contained In A Text String? on Spreadsheetpage. That inspired me to create an excel formula doing the same thing as the vba function.

In short, from the above blog post

The function,  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

The ExactWordInString function looks for a complete word -- not text that might be part of a different word.


Edit: Read Rick Rothstein (MVP - Excel) comments at the bottom of this blog post.

Here is the formula I created in C2:

=IF(ISNUMBER(FIND(B2, A2)), IF(COUNT((IF(CODE(MID(A2, FIND(B2, A2)-1, 1))>122, 1, "")), (IF(CODE(MID(A2, FIND(B2, A2)-1, 1))<65, 1, "")), (IF(FIND(B2, A2)-1<1, 1, ""))), IF(COUNT((IF(CODE(MID(A2, FIND(B2, A2)+LEN(B2), 1))>122, 1, "")), (IF(CODE(MID(A2, FIND(B2, A2)+LEN(B2), 1))<65, 1, "")), (IF((FIND(B2, A2)+LEN(B2)+1)>LEN(A2), 1, ""))), TRUE, FALSE), FALSE), FALSE)

copied down as far as necessary.

Download excel sample file for this tutorial.
(Excel 97-2003 Workbook *.xls)

Functions in this article:

Counts the number of cells within a range that meet the given condition

Returns the smallest number in a set of values. Ignores logical values and text

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

FIND(find_text, within_text, [start_num]) Returns the starting position of one text string within another text string. FIND is case-sensitive

COUNT(value1, [value2])
Counts the number of cells in a range that contain numbers

Returns a numeric code for the first character in a string, in the character set used by the computer

MID(text, start_num, num_chars)
Returns the characters in the middle of a text string, given a starting postion and length