How to test whether a cell contains text
Neither Excel nor Google Sheets has a CONTAINS function. The standard test is =IF(ISNUMBER(SEARCH("north",A2)),"Yes","No") — SEARCH returns the position if the text is present and #VALUE! if it is not, and ISNUMBER turns that into TRUE or FALSE. SEARCH is case-insensitive; swap in FIND when case matters.
Does A2 contain "north"?
=IF(ISNUMBER(SEARCH("north",A2)),"Yes","No")Case-insensitive. SEARCH returns a position number on a hit, #VALUE! on a miss.
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
The three ways to test for text
All three below do the same job. Which one to reach for depends on whether case matters and whether you want the shortest possible formula.
ISNUMBER(SEARCH(...)) is the general-purpose answer and the one to memorise. COUNTIF with wildcards is shorter and reads well, but it treats its criteria as a pattern, so a search term containing * or ? behaves unexpectedly. FIND is the case-sensitive variant of SEARCH and is otherwise identical.
Case-insensitive, general purpose
=ISNUMBER(SEARCH("north",A2))Case-insensitive, shorter
=COUNTIF(A2,"*north*")>0Careful if the search term itself contains * or ? — those become wildcards.
Case-sensitive
=ISNUMBER(FIND("North",A2))FIND distinguishes "North" from "north"; SEARCH does not.
Search term held in a cell
=ISNUMBER(SEARCH($E$1,A2))Contains any of several terms
Nesting three ORs quickly becomes unreadable. Passing an array of terms to SEARCH returns an array of results; SUM the ISNUMBER of that and test for more than zero.
The same pattern with a list held in a range scales to as many terms as you like, and lets non-technical colleagues edit the list without touching the formula.
Contains any of a fixed list
=SUMPRODUCT(--ISNUMBER(SEARCH({"north","south","east"},A2)))>0The double negative converts TRUE/FALSE to 1/0 so SUMPRODUCT can add them.
Contains any term from a range
=SUMPRODUCT(--ISNUMBER(SEARCH($E$1:$E$10,A2)))>0Contains ALL of several terms
=SUMPRODUCT(--ISNUMBER(SEARCH({"north","2026"},A2)))=2Compare against the number of terms rather than to zero.
Which term matched
=INDEX($E$1:$E$10,MATCH(TRUE,ISNUMBER(SEARCH($E$1:$E$10,A2)),0))Correct, but depends on values from your own sheet, so we cannot run it here.
Counting and summing on "contains"
COUNTIF and SUMIF accept wildcards directly when counting or summing rows whose labels contain text; a SEARCH or ISNUMBER wrapper is not needed. The criterion "*north*" matches north anywhere in a label, including northern, and these comparisons are case-insensitive. COUNTIF counts the matching cells, while SUMIF adds the corresponding values from its sum range. To use a keyword entered in D2, build the criterion as "*"&D2&"*". Keep the criteria and sum ranges aligned, and escape literal wildcard characters with a tilde when the keyword contains * or ?.
Count rows containing the text
=COUNTIF(A:A,"*north*")Sum where the label contains the text
=SUMIF(A:A,"*north*",B:B)Count rows containing text from a cell
=COUNTIF(A:A,"*"&E1&"*")Count case-sensitively
=SUMPRODUCT(--ISNUMBER(FIND("North",A2:A100)))COUNTIF is always case-insensitive, so a case-sensitive count needs this form.
Starts with, ends with, and exact matches
"Contains" is one of four related questions, and using the wrong one is a common source of quietly wrong results — "contains" will happily match in the middle of a longer word.
Starts with
=COUNTIF(A2,"north*")>0Ends with
=COUNTIF(A2,"*north")>0Exactly equals, case-insensitive
=A2="north"Exactly equals, case-sensitive
=EXACT(A2,"north")The = operator ignores case in both products; EXACT does not.
Contains as a whole word, not inside another
=ISNUMBER(SEARCH(" north ", " "&A2&" "))Padding both sides with spaces stops "north" matching inside "northern".
Common mistakes
Using ISNUMBER(SEARCH(...)) without an IF and expecting "Yes"/"No" — it returns TRUE/FALSE, which is often all you need but reads oddly in a report column.
Forgetting that SEARCH returns #VALUE! rather than FALSE on a miss. Testing =SEARCH("north",A2)>0 propagates the error instead of returning FALSE, which is why the ISNUMBER wrapper is not optional.
Expecting COUNTIF wildcards to work against numbers. Wildcards apply to text only; =COUNTIF(A:A,"*5*") will not find the number 15.
Wrong — propagates #VALUE! on a miss
=IF(SEARCH("north",A2)>0,"Yes","No")Shown as a mistake to avoid — do not use this one.
Right
=IF(ISNUMBER(SEARCH("north",A2)),"Yes","No")Find a digit inside a number
=ISNUMBER(SEARCH("5",TEXT(A2,"0")))Convert the number to text first, then wildcards and SEARCH behave.
Choosing the right test
| Question | Formula | Case-sensitive? |
|---|---|---|
| Contains text | =ISNUMBER(SEARCH("x",A2)) | No |
| Contains text | =ISNUMBER(FIND("x",A2)) | Yes |
| Contains text (short) | =COUNTIF(A2,"*x*")>0 | No |
| Starts with | =COUNTIF(A2,"x*")>0 | No |
| Ends with | =COUNTIF(A2,"*x")>0 | No |
| Equals exactly | =EXACT(A2,"x") | Yes |
| Contains any of a list | =SUMPRODUCT(--ISNUMBER(SEARCH(list,A2)))>0 | No |
Frequently asked
Is there a CONTAINS function in Excel?
No. Excel and Google Sheets both lack a CONTAINS function. Use =ISNUMBER(SEARCH("text",A2)) for a case-insensitive test, or =COUNTIF(A2,"*text*")>0 as a shorter equivalent.
What is the difference between SEARCH and FIND?
SEARCH is case-insensitive and supports wildcards; FIND is case-sensitive and does not. Both return the position of the match, or #VALUE! when there is none.
How do I check if a cell contains any one of several words?
Pass the words as an array: =SUMPRODUCT(--ISNUMBER(SEARCH({"north","south"},A2)))>0. Replace the array with a range reference to keep the list editable.
Why does my SEARCH formula return #VALUE!?
Because the text is not present — that is SEARCH signalling a miss, not a broken formula. Wrap it in ISNUMBER so a miss becomes FALSE instead of an error.
Do these work in Google Sheets?
Yes. SEARCH, FIND, ISNUMBER, COUNTIF, SUMPRODUCT and EXACT all behave the same way in Google Sheets. Sheets also offers REGEXMATCH, which is often tidier for complex patterns.
Stop writing these by hand
Describe what you need in plain English and FormulaCraft returns the formula, already run through a real spreadsheet engine. Free, no account.
Related
Last reviewed 2026-08-23. Every formula here is executed against sample data by our verification engine as part of the build. The few that cannot be — because they need values from your own sheet, or because the engine has no implementation for that form — say so directly underneath, rather than being counted as verified.