How to / Text
How to check if a cell contains specific text in Excel
Use =ISNUMBER(SEARCH("urgent", A2)). It gives TRUE when the word appears anywhere in the cell, in upper or lower case.
Does the note mention "urgent"?
| A | B | C | |
|---|---|---|---|
| 1 | Note | ||
| 2 | Urgent: call back | TRUE | |
| 3 | Invoice paid | ||
| 4 | urgent - late |
In C2: =ISNUMBER(SEARCH("urgent", A2)) → TRUE
How it works
- SEARCH looks for the word inside the cell and returns the position where it starts: a number.
- When the word is not there, SEARCH returns an error.
- ISNUMBER turns those two outcomes into TRUE and FALSE.
Variations
Yes or No in place of TRUE or FALSE
A B C 1 Note 2 Urgent: call back No 3 Invoice paid 4 urgent - late In C2:
=IF(ISNUMBER(SEARCH("urgent", A3)), "Yes", "No")→ NoCount the cells that contain it
A B C 1 Note 2 Urgent: call back 2 3 Invoice paid 4 urgent - late In C2:
=COUNTIF(A2:A4, "*urgent*")→ 2The asterisks stand for any text before and after the word.
A case-sensitive check
A B C 1 Note 2 Urgent: call back FALSE 3 Invoice paid 4 urgent - late In C2:
=ISNUMBER(FIND("urgent", A2))→ FALSEFIND tells "urgent" from "Urgent"; SEARCH does not.
Either of two words
A B C 1 Note 2 Urgent: call back Flag 3 Invoice paid 4 urgent - late In C2:
=IF(OR(ISNUMBER(SEARCH("urgent", A2)), ISNUMBER(SEARCH("late", A2))), "Flag", "")→ FlagList the rows that contain it
A B C 1 Note 2 Urgent: call back Urgent: call back 3 Invoice paid urgent - late 4 urgent - late In C2:
=FILTER(A2:A4, ISNUMBER(SEARCH("urgent", A2:A4)))→ Urgent: call back; urgent - lateExcel 2021 and Microsoft 365.
Watch out for
- Using = with wildcards.
=A2="*urgent*"does not work: the equals sign compares the whole cell with those exact characters. Wildcards only work inside functions such as COUNTIF and SEARCH. - Part of a longer word. Searching for "late" also finds "plate" and "later". Search for the word with spaces around it if that matters.
Doesn't work on your data? Paste your formula into the formula checker with a few rows of your sheet. It points at the part that fails and says why.
Functions used
- SEARCH: Position of one text inside another (not case-sensitive, allows wildcards).
- ISNUMBER: TRUE if the value is a number (dates count too).
- FIND: Position of one text inside another (case-sensitive).
- COUNTIF: Counts the cells that meet a condition.
- IF: Returns one value if a condition is TRUE and another if it is FALSE.
- FILTER: Keeps only the rows that meet a condition. The result spills.