The SEARCH function in Excel
SEARCH returns the position of one piece of text inside another, ignoring upper and lower case. It also understands wildcards, so ? stands for any single character and * for any run of characters. Its most common job is the "contains" test: ISNUMBER(SEARCH("refund",A2)) tells you whether a word appears anywhere in a cell.
Syntax
=SEARCH(find_text, within_text, [start_num])
| find_text | The text to look for, in quotes. Can include ? and * wildcards. |
|---|---|
| within_text | The cell or text to search in. |
| start_num | Optional. The position to start looking from. Defaults to 1. |
Try it yourself
This is a live spreadsheet. Type the formula and press Enter.
In C2, use IF, ISNUMBER and SEARCH to label the ticket "Billing" if the subject in A2 contains the word "invoice" (in any case), and "General" otherwise.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Queue | ||||||
| 2 | Leah | ||||||
| 3 | Tariq | ||||||
| 4 | Leah | ||||||
| 5 | Sven | ||||||
| 6 | Tariq | ||||||
| 7 | |||||||
| 8 | |||||||
| 9 |
Tip: Build it from the inside out: SEARCH for the word, ISNUMBER to get TRUE or FALSE, then IF for the two labels.
Examples
=SEARCH("refund",A3)→ 19SEARCH ignores case, so it finds "REFUND" and returns its position, 19. FIND would return #VALUE! here.
=IF(ISNUMBER(SEARCH("refund",A2)),"Billing","Other")→ "Billing"The "contains" pattern: SEARCH gives a number if the word is there, ISNUMBER turns that into TRUE, and IF picks the label.
=ISNUMBER(SEARCH("order ????",A2))→ TRUEEach ? matches exactly one character, so this checks for "order" followed by a space and four more characters, like an order number.
Example data
| 1 | Ticket subject | Plan |
|---|---|---|
| 2 | Refund request for order 5521 | Pro |
| 3 | Login issue after REFUND | Basic |
| 4 | Invoice missing VAT number | Team |
Common mistakes
#VALUE! when nothing matches
SEARCH returns #VALUE! if the text isn't found. Wrap it in ISNUMBER for a TRUE/FALSE answer, or IFERROR for a fallback.
Short words match inside longer ones
SEARCH("art",A2)also matches "Smart" and "Start". Use a longer, more specific term, or include a space, like" art ".Looking for a real ? or *
In SEARCH,
?and*are wildcards. Put a tilde in front to search for the character itself:SEARCH("~?",A2).Using SEARCH when case matters
SEARCH treats "VIP" and "vip" as the same. If capitals matter, for example in case-sensitive codes, use FIND.
FAQ
What's the difference between SEARCH and FIND?+
SEARCH ignores case and supports wildcards. FIND is case-sensitive and treats every character literally.
How do I check if a cell contains one of several words?+
Combine the tests with OR: =OR(ISNUMBER(SEARCH("refund",A2)),ISNUMBER(SEARCH("chargeback",A2))).
How do I count cells that contain a word?+
COUNTIF with wildcards does it in one step: =COUNTIF(A2:A50,"*refund*"). It ignores case, just like SEARCH.
Lesson
Master SEARCH step by step: FIND and SEARCH
Locate a character or word inside text with FIND or SEARCH, then use that position to split names and pull out order numbers.