IF Cell Contains Text Generator
Return one value when a cell contains a piece of text, and another when it does not.
IF Cell Contains
Return one value when a cell contains a piece of text, and another when it does not.
The fragment to search for. Needs quotes, or point at a cell.
The cell whose text is being checked.
Returned when the text appears anywhere in the cell.
Returned otherwise. Use "" for a blank cell.
=IF(ISNUMBER(SEARCH("urgent", A2)), "Yes", "")Worked Example
A support log with subject lines in column A, flagging anything mentioning "refund".
=IF(ISNUMBER(SEARCH("refund", A2)), "Refund", "")Returns: Shows "Refund" for a subject like "Please process my refund", and stays blank otherwise. Case does not matter.
Checks Before You Paste
- •SEARCH ignores case, so "urgent" also matches "Urgent" and "URGENT". Swap SEARCH for FIND if you need the match to be case-sensitive.
- •The ISNUMBER wrapper is what makes this work: SEARCH returns the position of the match, or a #VALUE! error when there is none, so testing it directly with IF would surface the error instead of FALSE.
- •SEARCH accepts wildcards - "a?c" matches one character between a and c, and "a*c" matches any run. To find a literal ? or *, prefix it with a tilde.
How IF Cell Contains works
Availability: IF, ISNUMBER, SEARCH and FIND are in every version of Excel, in Excel for Mac and for the web, and in Google Sheets. Nothing in this pattern needs Microsoft 365. Excel 365 users have TEXTBEFORE, TEXTAFTER and REGEXTEST as alternatives for some cases, but the ISNUMBER/SEARCH construction remains the portable one.
Excel has no CONTAINS function, so "does this cell contain that word" is built from two pieces. SEARCH looks for the text and reports the position where it starts. ISNUMBER converts that into TRUE or FALSE. IF then turns the TRUE or FALSE into whatever you want to see. Read the formula from the inside out and it says exactly that: search, did it find a number, if so show this.
The ISNUMBER wrapper is not decoration. When SEARCH fails to find the text it returns #VALUE! rather than 0 or FALSE, so =IF(SEARCH("refund", A2), "Yes", "No") fills the column with errors on every row that does not match. ISNUMBER absorbs that: a position is a number and gives TRUE, an error is not a number and gives FALSE. Any formula built on SEARCH for a yes/no answer needs it.
SEARCH ignores case and accepts wildcards. FIND does neither. Swapping one for the other in the same formula is the only change needed to make the test case-sensitive, which matters for currency codes, ticker symbols and part numbers where USD and usd are not the same thing. SEARCH's wildcard support cuts both ways - a question mark or asterisk in your search term is treated as a pattern, and needs a tilde in front to be taken literally.
For a single keyword, COUNTIF with wildcards does the same job in fewer functions: =IF(COUNTIF(A2, "*refund*"), "Yes", "No"). It is case-insensitive like SEARCH, but it silently ignores anything past 255 characters and it will not match digits inside a numeric cell, because wildcards apply to text only. For several keywords at once, an array constant inside SEARCH wrapped in SUMPRODUCT scales better than a stack of nested IFs.
Syntax
=IF(ISNUMBER(SEARCH(find_text, within_text, [start_num])), value_if_found, value_if_not_found)| Argument | Required | What it does |
|---|---|---|
find_text | Required | The fragment to look for, in double quotes or as a cell reference. Case is ignored. The wildcards ? (any single character) and * (any run of characters) are active; prefix with ~ to search for a literal ? or *. An empty find_text matches at position 1, so a blank keyword cell reports found on every row. |
within_text | Required | The cell whose text is being searched. One cell, not a range - passing a range either intersects to a single row or spills an array in Microsoft 365. |
start_num | Optional | SEARCH's optional third argument: the character position to begin searching from, default 1. Useful for finding the second occurrence of something by starting just past the first. Values below 1 or beyond the length of the text return #VALUE!. |
value_if_found | Required | What IF returns when the text is present - quoted text, a number, a cell reference, or another formula. |
value_if_not_found | Required | What IF returns otherwise. Use "" for a cell that appears blank. Omitting this argument makes IF return the logical FALSE, which is rarely what a report should display. |
More worked examples
A2 holds the support ticket subject Duplicate Invoice Received, and tickets need routing to a team based on a keyword.
=IF(ISNUMBER(SEARCH("invoice", A2)), "Billing", "General")Returns: Billing
The capital I in the subject line is irrelevant because SEARCH ignores case. A subject reading Invoicing question would also match, since SEARCH looks for the fragment anywhere in the string rather than a whole word.
A2 holds a transaction description where currency codes appear in capitals - USD 450.00 - and lower-case usd elsewhere in the text must not trigger a match.
=IF(ISNUMBER(FIND("USD", A2)), "US dollars", "Other currency")Returns: US dollars for USD 450.00, and Other currency for a description reading paid in usd equivalent.
FIND is the case-sensitive twin of SEARCH and takes the same three arguments. It also has no wildcard behaviour, which makes it the safer choice when the search term contains an asterisk or question mark.
A2 holds an email subject line and any of three words should mark it as high priority.
=IF(SUMPRODUCT(--ISNUMBER(SEARCH({"urgent","asap","critical"}, A2)))>0, "Priority", "Normal")Returns: Priority when the subject contains any one of the three words, Normal otherwise.
SEARCH against an array constant returns three results at once; the double unary turns TRUE/FALSE into 1/0 and SUMPRODUCT adds them. Change >0 to =3 and the test becomes all three rather than any. Point the array at a range of keyword cells instead of a constant to make the list editable.
Common mistakes
- Dropping ISNUMBER and getting #VALUE! on every non-matching row
- SEARCH returns the starting position when it finds the text and the #VALUE! error when it does not - it never returns FALSE. =IF(SEARCH("refund", A2), "Yes", "No") therefore errors out on exactly the rows where the answer should be No. ISNUMBER is what converts the error into a clean FALSE.
- An empty keyword cell marks every row as found
- SEARCH("", A2) returns 1, because an empty string is found at the start of everything. Point find_text at a cell someone has not filled in yet and the whole column reports Yes. Guard it: =IF($D$1="", "", IF(ISNUMBER(SEARCH($D$1, A2)), "Yes", "No")).
- Wildcards firing when you meant a literal character
- Searching for "3*5" with SEARCH matches 345, 3125 and anything else with a 3 before a 5, because * means any run of characters. Escape it with a tilde - SEARCH("3~*5", A2) - or use FIND, which treats every character literally.
- Matching a fragment inside a longer word
- SEARCH("art", A2) is TRUE for cart, start and department. There is no whole-word option. Pad the search with spaces - SEARCH(" art ", " " & A2 & " ") - so the fragment has to stand alone, adding the outer spaces to catch matches at the very start or end of the cell.
- Passing a range where a single cell belongs
- =IF(ISNUMBER(SEARCH("refund", A2:A100)), "Yes", "No") does not scan the column. Older Excel intersects to one row or returns #VALUE!; Microsoft 365 spills one answer per row, which may look like it worked until the formula is copied. To ask whether any cell in a range contains the text, use =SUMPRODUCT(--ISNUMBER(SEARCH("refund", A2:A100)))>0.
Frequently Asked Questions
Because SEARCH signals failure with the #VALUE! error rather than with FALSE. IF passes that error straight through, so every row without a match shows #VALUE! instead of your not-found value. ISNUMBER asks a question SEARCH can always answer - is this result a number - and turns it into the TRUE or FALSE that IF expects.
Replace SEARCH with FIND: =IF(ISNUMBER(FIND("USD", A2)), "Yes", "No"). The arguments are identical, so nothing else changes. Bear in mind FIND also drops wildcard support, which is an advantage when the search term contains an asterisk and a limitation when you were relying on patterns.
Search against an array constant and add up the hits: =IF(SUMPRODUCT(--ISNUMBER(SEARCH({"urgent","asap"}, A2)))>0, "Priority", "Normal"). This scales to as many terms as you like and can point at a range of keyword cells instead of a hard-coded list. Nesting OR(ISNUMBER(SEARCH(...)), ISNUMBER(SEARCH(...))) works too but gets unreadable past three terms.
COUNTIF is shorter - =IF(COUNTIF(A2, "*refund*"), "Yes", "No") - and reads well for a single keyword. It has two limits worth knowing: it only examines the first 255 characters of a cell, and its wildcards apply to text, so it will not find digits inside a cell holding a real number. SEARCH has neither restriction and is the safer default for long text or mixed data.
Wrap the array search in an INDEX/MATCH over the keyword list: =IFERROR(INDEX($D$2:$D$10, MATCH(TRUE, ISNUMBER(SEARCH($D$2:$D$10, A2)), 0)), "None"). It returns the first keyword from the list that appears in the cell. In Excel 2019 and earlier this needs entering with Ctrl+Shift+Enter as an array formula.
Related Tools
IF Formula Generator
Build the surrounding IF, including nested tests for more than two outcomes.
COUNTIF Generator
Count how many cells in a range contain the text, rather than testing one at a time.
Filter Excel Rows
Keep only the rows whose text matches, working on an uploaded file directly.
ISBLANK Generator
Swap the SEARCH test for ISBLANK, ISNUMBER or ISERROR in the same IF shape.