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:
| Name | Contains “Mr” |
|---|---|
| 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 top-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.
Another hint
Scroll down for the second hint.
Hint #2
So the other function that you want to use is ISNUMBER. Here’s the syntax for Isnumber:
=ISNUMBER(Value)
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.
The answer to this question doesn’t really make sense to me. If isnumber is supposed to give a true or false answer if there is a number or not, then why does this formula even work if there aren’t any numbers. The answer I came up with is, =AND(LEFT(A2,2)=”MR”).
Please keep in mind I am a novice only been using excel for a few months. This is simply a learning question not critical. Thanks and Have a great day.
exercises are very much helpful.
IT LOOK LIKE A DIFFICULT PROBLEM BUT SOLUTION IS EASY AND SIMPLE
exercises are very much helpful.