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.
In C2, show Refund next to every call note that mentions a refund anywhere, whatever the capitals, and leave the cell blank otherwise.
| A | B | C | |
|---|---|---|---|
| 1 | Call | Note | Flag |
| 2 | C401 | Left a voicemail | |
| 3 | C402 | Called back, no answer | |
| 4 | C403 | Refund sent on Friday | |
| 5 | C404 | Delivery delayed again | |
| 6 | C405 | Changed delivery address | |
| 7 | C406 | wants a refund for the second box | |
| 8 | C407 | REFUND agreed by manager | |
| 9 | C408 | Delivery delayed again | |
| 10 | C409 | Invoice needs a PO number | |
| 11 | C410 | Sent 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…?
| A | B | C | |
|---|---|---|---|
| 1 | Note | ||
| 2 | Refund sent | ||
| 3 | Late delivery | ||
| 4 | Asked for a REFUND | ||
| 5 | K12 |
=SEARCH("refund",A2)in . "refund" starts at character 1 of "Refund sent". Capitals don't matter to SEARCH.=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.=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
- In C2, show Refund next to every call note that mentions a refund anywhere, whatever the capitals, and leave the cell blank otherwise.
- 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.
- 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.
- 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.
- 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.