How to Check if a Cell Contains a Substring in Excel or Google Docs

• The Trusty SEARCH Function
• A Simple Example
• Adding the ISNUMBER Function

If you’ve ever been in a situation where you’re working with (yet another) spreadsheet in Excel or Google Docs and can’t figure out how to determine if a particular cell contains a keyword or phrase you’re searching for, then you’ve come the right place.

Surprisingly, neither application provides a built-in SUBSTRING search function, but we can easily replicate that same functionality without too much trouble.

The Trusty SEARCH Function

At the core, we can determine if a cell contains some particular text by making use of the SEARCH function. The arguments are the same for both Excel and Google Docs and look like this:

SEARCH(find_text, within_text, [start_at])

• find_text is the text you are searching for.
• within_text is the text or cell you are searching in.
• start_at is an optional argument that allows you to specify the character number of the within_text at which point to begin the search. By default this is assumed to be 0, meaning it will search the entire string.

A Simple Example

We have a basic spreadsheet created for this example that lists books and their associated authors:

Now let’s add another cell off to the side for the text we want to search for, along with two new columns where we’ll perform our SEARCH function above to see if that value (the title or author) contains the search for text.

The actual formula within the Title Match column looks like the following, using our SEARCH function to search the Title column cell of that row for the text within the Search for: box, which in this case is just the last name Tolkien:

=SEARCH($F$1, A2)

The problem we see here is the SEARCH function returns an error if no match is found. While the word Tolkien was found as part of the first and second author cells (starting at character number 8), it failed to find any matches in the other cells and thus produced that ugly error, which isn’t any good for us.

Adding the ISNUMBER Function

The simplest solution here is to wrap the above SEARCH($F$1, A2) function in yet another function which will convert the output from the SEARCH function into a boolean (true or false) value. For this purpose, we’ll use the ISNUMBER function. As the name suggests, this function simply checks if the provided value is, in fact, a number or not, and returns a value of TRUE or FALSE accordingly. Thus ISNUMBER(5) will return TRUE, while ISNUMBER('ERROR') will return FALSE.

Now our above SEARCH function is wrapped in ISNUMBER and looks like this:

=ISNUMBER(SEARCH($F$1, A2))

Updating all the cells in our table accordingly gets rid of the ugly errors and provides a simple TRUE or FALSE result in the Match columns:

There we have it! A simple yet effective combination of functions to allow you to easily determine if a particular cell contains a substring of text and returns a useful boolean value as a result.