Now we get TRUE if "apple" is found, FALSE if not:
Note that COUNTIF supports wildcards – in fact, you must use wildcards to get the "contains" behavior, by adding an asterisk to either side of the text you're looking for. On the downside, COUNTIF isn't case-sensitive, so you'll need to use FIND if case is important.
So what can you do with these kind of formulas? A lot!
Here are a few examples (with full explanations) to inspire you: