ISNUMBER and SEARCH: Check if a Cell Contains Text

ISNUMBER with SEARCH is the standard Excel way to check if a cell contains some text: =ISNUMBER(SEARCH("refund",B2)) returns TRUE when "refund" appears anywhere in B2 and FALSE when it doesn't. It works in every Excel version and ignores capitals. This guide explains why the two functions work together, then shows the test inside IF, with several words, case-sensitive, and in FILTER and counts.

How ISNUMBER and SEARCH work together#

SEARCH finds text and returns a position; ISNUMBER turns that position into TRUE or FALSE. On their own, each function is only half the answer.

SEARCH looks for one piece of text inside another:

=SEARCH(find_text, within_text, [start_num])

If it finds the text, it returns the position of the first character. If not, it returns a #VALUE! error. ISNUMBER checks whether a value is a number and returns TRUE or FALSE. It never returns an error itself.

Put together, the two possible SEARCH results become a clean answer:

The SEARCH function guide and the ISNUMBER function guide cover each function's arguments.

The sample data#

The examples below use this list of support tickets in A1:B7:

Three subjects mention a refund (rows 2, 4 and 6), with different capitals and in different places.

How to return your own labels with IF#

TRUE and FALSE are fine for checking, but reports usually need words. Wrap the test in IF:

=IF(ISNUMBER(SEARCH("refund",B2)),"Refund","Other")

Fill it down (Windows: Ctrl+D; Mac: ⌘D). Rows 2, 4 and 6 show Refund; the rest show Other.

You don't need =TRUE inside the IF. ISNUMBER already returns TRUE or FALSE, which is exactly what IF's first argument expects. To read the search word from a cell instead, such as E1, write SEARCH($E$1,B2). Lock it with $ so it stays put as you fill down.

How to make the check case-sensitive#

Use FIND instead of SEARCH. FIND works the same way, but "Refund" and "REFUND" are different to it.

=ISNUMBER(FIND("Refund",B2))

Only row 2 returns TRUE. Row 4 has "refund" in lower case and row 6 has "REFUND" in capitals, so FIND returns #VALUE! and ISNUMBER returns FALSE. The comparison of FIND vs SEARCH covers the other differences, such as wildcards.

How to check that a cell does not contain text#

Wrap the test in NOT to flip TRUE and FALSE. This returns TRUE for the subjects that don't mention a refund:

=NOT(ISNUMBER(SEARCH("refund",B2)))

Rows 3, 5 and 7 return TRUE. You may also see =ISERROR(SEARCH("refund",B2)), which gives the same result, because SEARCH returns an error exactly when the text is missing. Either works; the NOT version reads more clearly next to the "contains" formulas. Use it in IF to label the rest, or in FILTER to list the tickets that are not refunds.

How to check for several words#

Join tests with OR when any of the words is enough, and with AND when all must appear.

=OR(ISNUMBER(SEARCH("refund",B2)),ISNUMBER(SEARCH("return",B2)))

This is TRUE for rows 2, 4 and 6. Row 6 contains both words, which still counts once.

=AND(ISNUMBER(SEARCH("return",B2)),ISNUMBER(SEARCH("refund",B2)))

This is TRUE only for row 6, the one subject that mentions both.

For a longer list of words, an array constant keeps the formula short:

=SUMPRODUCT(--ISNUMBER(SEARCH({"refund","return","cancel"},B2)))>0

SEARCH checks each word, ISNUMBER gives TRUE or FALSE for each, -- turns those into 1s and 0s, and SUMPRODUCT adds them. If the total is above 0, at least one word was found.

How to use the test in FILTER, counts and highlighting#

Because ISNUMBER(SEARCH()) returns TRUE or FALSE, it slots into any tool that needs a condition.

Count matching cells. This returns 3:

=SUMPRODUCT(--ISNUMBER(SEARCH("refund",B2:B7)))

=COUNTIF(B2:B7,"*refund*") gives the same count with wildcards, but the SEARCH version can be made case-sensitive by swapping in FIND.

Filter matching rows. In Excel 2021 or Microsoft 365:

=FILTER(A2:B7,ISNUMBER(SEARCH("refund",B2:B7)))

This spills the three refund tickets into a new list. The guide to filtering rows that contain text builds a search box with this pattern.

Highlight matching cells. Use the same test as a conditional formatting rule, as shown in how to highlight cells that contain specific text.

Common problems and how to fix them#

Most surprises with ISNUMBER SEARCH come from what SEARCH matches, not from ISNUMBER.

1. Every row returns TRUE. The search text is empty, often because it points at a blank cell. SEARCH("",B2) returns 1. Guard it: =AND($E$1<>"",ISNUMBER(SEARCH($E$1,B2))). A cell holding "" from a formula counts as empty text here too; see ISBLANK vs an empty string.
2. Partial words match. SEARCH finds text anywhere, so "art" matches "start" and "party". Search for " art " with spaces, or add a space to both sides of the cell: SEARCH(" art "," "&B2&" ").
3. Wildcards behave oddly. In SEARCH, ? and * are wildcards. To find a real question mark, put a tilde before it: SEARCH("~?",B2).
4. Numbers in the cell. SEARCH treats numbers as text, so SEARCH("44",B2) finds "4471". That is usually what you want, but be careful with short numbers.
5. Hidden spaces or line breaks. A word split by a line break may not match. Clean the text first with TRIM and CLEAN.

For more text-finding tricks, see how to find text with FIND and SEARCH.

Practise it#

Try the exercise on this page: build the IF, ISNUMBER and SEARCH formula from the inside out. Then practise FIND and SEARCH in a live spreadsheet to locate text and pull out parts of it, and practise IS functions and SWITCH to test values with ISNUMBER, ISTEXT and ISBLANK. Each lesson checks your formula instantly.