Excel SEARCH Function: find text inside a cell (and test if it’s there)
SEARCH looks for text inside a cell and returns the position where it starts. “pen” in “Blue Pen” starts at character 6.
The formula
=SEARCH(find_text, within_text, [start_num])
If the text is not there, you get #VALUE!.
SEARCH vs FIND
| SEARCH | FIND | |
|---|---|---|
| Upper/lower case | ignores (“pen” finds “Pen”) | must match exactly |
| Wildcards * ? | yes | no |
Use SEARCH by default. Use FIND when case matters.
Test if a cell contains a word
=ISNUMBER(SEARCH("pen",A2))
TRUE if “pen” is anywhere in A2. Wrap it in IF for a label:
=IF(ISNUMBER(SEARCH("pen",A2)),"Stationery","")
More ways: if cell contains text.
Contains any of several words
=SUMPRODUCT(--ISNUMBER(SEARCH({"pen","pencil","marker"},A2)))>0
Take the text after a character
Everything after the dash in “INV-2045”:
=MID(A2,SEARCH("-",A2)+1,100)
Everything before it: =LEFT(A2,SEARCH("-",A2)-1). See MID and LEFT.
Highlight rows that contain a word
Conditional Formatting › New Rule › Use a formula: =ISNUMBER(SEARCH("urgent",$C2)).
20 years with spreadsheets and enterprise systems. Writes one short, plain-English Excel lesson a day. Got an Excel question? learnexceleasycom@gmail.com
