IF With Text in Excel: Check Words and Partial Matches

To test if a cell contains text in Excel, you combine IF with a check that suits the job: = for an exact word, or ISNUMBER and SEARCH for a word anywhere in the cell. This guide is for anyone who knows the basic IF function and now needs it to work with words, such as labelling support tickets, flagging statuses or sorting product names.

How to use IF with text: exact matches#

To check whether a cell equals a word, compare it with the word in double quotes.

=IF(A2="Paid","Done","Chase")

If A2 is Paid, the result is Done; anything else gives Chase. Three rules to remember:

• Text inside a formula always needs double quotes: "Paid", not Paid. Without quotes Excel looks for a named range called Paid and returns #NAME?.
• The comparison ignores case: "paid", "Paid" and "PAID" all match.
• Spaces count: "Paid " with a trailing space does not match "Paid". Use =IF(TRIM(A2)="Paid","Done","Chase") to ignore extra spaces.

To test the opposite, use <> (not equal to): =IF(A2<>"Paid","Chase","Done"). The IF function guide covers the full syntax.

How to check if a cell contains text anywhere (partial match)#

To find a word anywhere inside a cell, use SEARCH inside ISNUMBER inside IF. Here is a list of support tickets:

In C2, type and fill down:

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

How it works, from the inside out:

1. SEARCH("refund",B2) looks for "refund" in B2. If it finds it, it returns the position (1 for "Refund for order 4412"). If not, it returns #VALUE!.
2. ISNUMBER(...) turns a position into TRUE and the #VALUE! error into FALSE.
3. IF(...) shows Refund for TRUE and Other for FALSE.

SEARCH ignores case, so "REFUND not received" also matches. Our guide to FIND and SEARCH in Excel explains both functions in more depth.

Why doesn't IF work with wildcards?#

The = comparison in IF doesn't understand wildcards. This formula looks for the literal text *delivery*, asterisks included, so it returns "No" for every row:

=IF(B5="*delivery*","Yes","No")

Wildcards only work in functions built for criteria, such as COUNTIF, SUMIF and XLOOKUP. That gives you a second way to write a "contains" test:

=IF(COUNTIF(B5,"*delivery*"),"Yes","No")

COUNTIF counts 1 if B5 contains "delivery" and 0 if not. IF treats 1 as TRUE and 0 as FALSE, so B5 ("Change delivery address") returns Yes. Both methods work in every Excel version; ISNUMBER(SEARCH()) is more common and easier to extend. See wildcards in Excel criteria for the * and ? rules.

How to make a case-sensitive text check#

Use FIND instead of SEARCH, or EXACT instead of =, when capitals matter.

With FIND, "REFUND not received" returns Other, because FIND treats R and r as different letters. Use it for codes where case matters, such as product IDs that use both. For a full side-by-side, see FIND vs SEARCH.

How to check for several words: OR and AND#

To flag a cell that contains any one of several words, put each test inside OR.

=IF(OR(ISNUMBER(SEARCH("refund",B2)),ISNUMBER(SEARCH("return",B2))),"Money back","Other")

Ticket T-105, "Return and refund request", returns Money back. To require all the words, swap OR for AND. For more than two results, such as Refund, Delivery and Other, nest IFs, putting the second IF in the "value if false" slot:

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

Excel checks the tests in order and stops at the first TRUE. Our guide to nested IF shows how to keep longer chains readable.

How to check whether a cell has any text, or is empty#

Sometimes you don't care which word is in the cell, only whether there is one.

• =IF(ISTEXT(A2),"Text","Not text") is TRUE only for text, not numbers.
• =IF(A2<>"","Filled","Empty") is TRUE for anything at all, text or numbers.
• =IF(LEFT(A2,2)="T-","Ticket","Other") checks how the text starts.

If you need to pick a label from a list by number instead, such as 1 = Morning, 2 = Afternoon, the CHOOSE function is shorter than a chain of IFs.

How to count or sum cells that contain text#

If you only need to know how many cells contain a word, you don't need IF or a helper column. COUNTIF with wildcards does it in one cell:

=COUNTIF(B2:B7,"*refund*")

This returns 3: tickets T-101, T-103 and T-105. The asterisks mean "any text before or after", and COUNTIF ignores case. SUMIF works the same way for totals: with amounts in column D, =SUMIF(B2:B7,"*refund*",D2:D7) adds the amounts of every refund ticket. To use a word typed in a cell, say F1, join the wildcards to it: "*"&F1&"*".

Common mistakes with IF and text#

• Missing quotes around the word, which gives #NAME?.
• Using * with =, which never matches (see above).
• Forgetting ISNUMBER: =IF(SEARCH("refund",B2),"Refund","Other") works when the word is found, but returns #VALUE! instead of "Other" when it isn't.
• Hidden spaces in imported data. Wrap the cell in TRIM before comparing.
• Short words matching inside longer ones. SEARCH("art",B2) also matches "start" and "party". Search for a longer phrase, or include a space, such as " art ", if that matters.
• Numbers stored as text. =IF(A2=100,...) won't match a cell holding the text "100". Compare with "100" or convert the cell first.

Practise it#

Try the live exercise on this page to label the refund tickets. Then practise the IF function in a live spreadsheet for pass/fail and over/under checks, and use the FIND and SEARCH lesson to get comfortable with the position numbers that make "contains" tests work.