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

If you’ve ever found yourself working on a spreadsheet in Excel or Google Docs and struggling to determine if a cell contains a specific keyword or phrase, then you’re in the right place.

Surprisingly, neither of these applications offers a built-in substring search function. However, we can easily reproduce this functionality without much difficulty.

The Trusty SEARCH Function

At the heart of the solution, we can determine if a cell contains a particular text by using the SEARCH function. The arguments are the same for Excel and Google Docs and look like this:

• search_text is the text you’re looking for.
• within_text is the text or cell in which you’re performing the search.
• start_at is an optional argument that allows you to specify the character number in the within_text to start the search from. By default, it is assumed to be 0, which means it will search the entire string.

A Simple Example

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

Now, let’s add another cell next to the text we want to search for, as well as two additional columns where we will perform our SEARCH function to see if this value (the title or the author) contains the searched text.

The actual formula in the “Match Title” column looks like this, using our SEARCH function to search the contents of the “Title” column for the text in the “Search:” cell, which in this case is simply the last name “Tolkien”.

The problem we see here is that the SEARCH function returns an error if no match is found. While the word “Tolkien” has been found in the first two author cells (starting from character number 8), no match has been found in the other cells, resulting in this ugly error, which is of no use to us.

Adding the ISNUMBER Function

The simplest solution is to wrap the above SEARCH($F$1, A2) function in another function that will convert the output of the SEARCH function into a boolean value (true or false). For this purpose, we will use the ISNUMBER function. As the name suggests, this function simply checks whether the given value is indeed a number or not and returns a TRUE or FALSE value 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))

By updating all the cells in our table accordingly, we eliminate the ugly errors and get a simple TRUE or FALSE result in the “Match” columns:

And voilà! A simple yet effective combination of functions that allows you to easily determine if a particular cell contains a substring of text and returns a useful boolean value as a result.

For more helpful tips and tricks in the world of spreadsheets, visit Crawlan.com.