The FIND, SEARCH guide
=ISNUMBER(SEARCH("refund", A2)) is TRUE when A2 contains refund in any capitals. =ISNUMBER(FIND("VAT", A2)) is TRUE only for VAT in capitals, so the "vat" inside Private doesn't count.
Same job, one difference
Both return the position where one text starts inside another: SEARCH("fund", "Refund") is 3. The difference is capitals.
FIND | SEARCH | |
|---|---|---|
| Capitals | count: "refund" doesn't find "Refund" | ignored |
| Wildcards | no: * is a star | yes: * and ?, with ~ before a real one |
| Not found | #VALUE! | #VALUE! |
Both take an optional third argument, where to start looking. The position they return is still counted from the first character.
Which one to use
Use SEARCH when people type the word in any case: refund, Refund, REFUND. Use FIND when the capitals carry the meaning. VAT, the tax, is always written in capitals, and searching for it without them also matches Private, Activate and Innovative.
TRUE or FALSE: wrap it in ISNUMBER
FIND and SEARCH don't return FALSE when the text isn't there; they return #VALUE!. ISNUMBER turns a position into TRUE and the error into FALSE, which is what a "does it contain" column needs. IF(ISNUMBER(SEARCH("refund", A2)), "Refunds", "") sends the ticket somewhere.
COUNTIF(A2, "*refund*") > 0 gives the same answer as SEARCH. It ignores capitals too, so it has the same problem with VAT.
Common mistakes
- FIND with the wrong capitals. Rows written "Refund" come out FALSE.
- SEARCH for a word in capitals. It also finds the same letters inside other words.
- No ISNUMBER. The column fills with positions and #VALUE!.
- IF(SEARCH(…) > 0, …). The comparison never sees FALSE: on rows without the word, the error comes straight through.
FIND also tells you where to cut text: split first and last names.
FAQ
- What is the difference between FIND and SEARCH in Excel?
- FIND counts capitals and doesn't allow wildcards. SEARCH ignores capitals and allows * and ?. Both return the position of the text, or #VALUE! if it isn't there.
- How do I check if a cell contains a word in Excel?
- Use =ISNUMBER(SEARCH("word",A2)). It returns TRUE if A2 contains the word in any capitals, and FALSE if it doesn't.
- Why does FIND return #VALUE!?
- The text isn't there, or it is but with different capitals. FIND is case-sensitive; SEARCH isn't.
- Is SEARCH case-sensitive?
- No. SEARCH("refund",A2) finds refund, Refund and REFUND.



































































