Course outline / Logical functions
Lesson 24 of 75 · Intermediate · 4 exercises
Does a cell contain a word?
Test whether text contains a word with SEARCH and ISNUMBER, and act on it with IF.
What you'll practise
- Find a word with SEARCH
- Turn it into TRUE or FALSE
- Label the matching rows
- Count them with a wildcard
Find a word with SEARCH
SEARCH returns the position where some text starts, ignoring capitals. If it's not there, it returns #VALUE!.
=SEARCH("cotton", A2)
In cell D2, find where "cotton" starts in A2.
Turn it into TRUE or FALSE
Wrap SEARCH in ISNUMBER: a position is a number, so the result is TRUE when the word is there and FALSE when it isn't.
In cell D2, check whether A3 contains "cotton".
Label the matching rows
In cell D2, show "Cotton" if A4 contains "cotton", otherwise "Other".
Count them with a wildcard
To count matches, COUNTIF with wildcards is simpler: "*cotton*" means cotton anywhere in the text.
In cell D2, count the products in A2:A5 that contain "cotton".
Try it in a real spreadsheet
Every step checks your answer instantly and tells you exactly what to fix.