Does Excel contain a function that checks if a cell contains text? No. Should it? Absolutely. Like we’ve done before, with other functions that don’t exist (IFNOTERROR, ISNOTERROR) we’re going to create the Excel Contains function ourselves. It’s not that hard!
Excel CONTAINS function
So we want a formula that returns TRUE if a cell contains a specific text, and FALSE if it doesn’t.
For example, we may have a list of names with Mr (for Mister) and Ms (for Miss) in front of the name.
To determine the gender, we could check if the text “Mr” is used in a cell to determine if the person is male. Like this:
|Mr. James Gordon||TRUE|
|Ms. Anna Young||FALSE|
|Ms. Annie Hensley||FALSE|
|Mr. Tyler Patel||TRUE|
Alright, so how do we make this function? Well if you just want the answer, you can click the “Show answer” button in the bottom right of the exercise below.
But if you want to try it for yourself, here’s a hint: you need to combine two functions. One of them is called SEARCH, which works like this:
=SEARCH(Find text, Within text)
It returns the index at which the text is found. If you need more hints, you can keep reading below the exercise.
Scroll down for the second hint.
So the other function that you want to use is ISNUMBER. Here’s the syntax for Isnumber:
This will return if the given value is a number (TRUE) or not (FALSE). Think about how you can combine this with SEARCH to get the CONTAINS function.