Confident Cells

Searching text and wildcards: practice exercises

SEARCH, FIND, ISNUMBER(SEARCH()), ?, * in lookups. 5 exercises on real-looking data, each marked the moment you press Check. The first exercise is below to try now; the full lesson is part of Pro.

Your turn

In C2, show Refund next to every call note that mentions a refund anywhere, whatever the capitals, and leave the cell blank otherwise.

ABC
1CallNoteFlag
2C401Left a voicemail
3C402Called back, no answer
4C403Refund sent on Friday
5C404Delivery delayed again
6C405Changed delivery address
7C406wants a refund for the second box
8C407REFUND agreed by manager
9C408Delivery delayed again
10C409Invoice needs a PO number
11C410Sent a copy of the invoice

How it works

SEARCH(find, text) gives the position where find starts inside text, or #VALUE! if it isn't there. Capitals don't matter. FIND does the same but does care about capitals. To ask "does this contain…?", use ISNUMBER(SEARCH(...)).

Wildcards work in COUNTIF, SUMIF, VLOOKUP and MATCH: * is any run of characters and ? is exactly one character. XLOOKUP needs match mode 2 to use them.

=SEARCH("ref",A2) =ISNUMBER(SEARCH("ref",A3)) =COUNTIF(A2:A9,"???") =XLOOKUP("*"&E1&"*",A2:A9,B2:B9,"Not found",2)

Finding text inside text

SEARCH(find, text) looks for find inside text and gives the position where it starts: SEARCH("ref","Refund") is 1. If it isn't there, you get #VALUE!.

SEARCH ignores capitals. FIND works the same way but treats "Refund" and "refund" as different.

Worked example: does it contain…?

ABC
1Note
2Refund sent
3Late delivery
4Asked for a REFUND
5K12
  1. =SEARCH("refund",A2) in . "refund" starts at character 1 of "Refund sent". Capitals don't matter to SEARCH.
  2. =SEARCH("refund",A2) in . Copied down, "Late delivery" has no refund in it, so SEARCH gives #VALUE!. In "Asked for a REFUND" it starts at character 11.
  3. =ISNUMBER(SEARCH("refund",A2)) in . A number means it was found. Wrap SEARCH in ISNUMBER to get TRUE or FALSE, and put that inside IF if you want words.

Wildcards

  • * stands for any number of characters, including none. "Oak*" starts with Oak; "*oak*" contains oak.
  • ? stands for exactly one character. "???" matches any three-character entry, and "K??" a K and two more.
  • COUNTIF, SUMIF, VLOOKUP and MATCH understand wildcards straight away. XLOOKUP only does when you set its fifth argument, match mode, to 2.

What you'll practise

  1. In C2, show Refund next to every call note that mentions a refund anywhere, whatever the capitals, and leave the cell blank otherwise.
  2. Type any word into H1 and column C should show which notes mention it. In C2, show TRUE if the note contains the word in H1, whatever the capitals, and FALSE if not.
  3. Permanent staff have a letter and 3 digits in their code, such as K482. Contractors have a letter and 4 digits, such as K4821. In H1, use COUNTIF with ? wildcards to count the contractors.
  4. Make a search box. In H2, use XLOOKUP to find the price of the item whose name contains the word in H1, showing Not found if there isn't one.
  5. Someone can only remember how a customer's name starts. In H2, use VLOOKUP with a wildcard to find the phone number of the customer whose name starts with the text in H1.