Excel · ChatGPT

Excel IF Cell Contains Text

Test whether a cell contains a word or character sequence, then return a flag or count matching cells. Choose SEARCH for case-insensitive checks, FIND for case-sensitive checks, and COUNTIF for wildcard counts.

Choose your situation — copy the tested prompt:

In Excel, cells A2:A5 contain:

Payment overdue - contact customer
OVERDUE invoice escalated
Payment received
[blank]

I want column B to return "Review" when the cell contains the word/string "overdue" anywhere, regardless of letter case, and return a blank otherwise.

Give the exact formula for B2 using IF, SEARCH, and ISNUMBER, then copy it down. State the expected result for every row and explain how blank cells behave. Do not use an exact-equality test.

Test 1 of 3 — approved PASS and preserved as tested.

Cells A2:A5 contain:

Status: HOLD
Status: hold
ShareHOLDER review
HOLD - legal review

I need a case-sensitive flag that returns TRUE when the exact uppercase character sequence "HOLD" occurs anywhere in the cell.

Give the formula using FIND and ISNUMBER and state the expected TRUE/FALSE result for each row. Explicitly explain that this is a substring test, so "ShareHOLDER" also contains the sequence HOLD. Then show, as a separate optional note, how the requirement would need to change if I wanted the standalone word HOLD rather than any substring. Do not silently treat substring matching as whole-word matching.

Test 2 of 3 — approved PASS and preserved as tested.

Cells A2:A5 contain:

Ticket * pending
Ticket 42 pending
Use ? placeholder
No special symbol

I need:
1. a COUNTIF formula that counts cells containing the word/string "pending" anywhere;
2. a separate COUNTIF formula that counts cells containing a literal asterisk character, not any sequence of characters.

Give both exact formulas and their expected counts. Explain how Excel wildcards work in COUNTIF and why the tilde is required before a literal asterisk. Also state whether COUNTIF text matching is case-sensitive.

Test 3 of 3 — approved PASS and preserved as tested.

Before — overdue · OVERDUE · received · blank

Source condition	Risk
overdue · OVERDUE · received · blank	Exact equality misses text embedded inside longer descriptions.

After — verified result

Output	Expected result
Flag result	Review · Review · blank · blank using SEARCH

Before — HOLD · hold · ShareHOLDER

Source condition	Risk
HOLD · hold · ShareHOLDER	Case and substring boundaries change the meaning of a match.

After — verified result

Output	Expected result
Case-sensitive	TRUE · FALSE · TRUE using FIND

Before — pending and literal *

Source condition	Risk
pending and literal *	COUNTIF wildcard * can mean any character sequence.

After — verified result

Output	Expected result
Two counts	pending = 2 · literal asterisk = 1 using ~*

Diagnosis — what can make this workflow unsafe

Exact equality

A2="overdue" cannot find a word inside a longer cell

Wrong test

SEARCH vs FIND

SEARCH ignores case; FIND distinguishes it

Choose first

Substring ambiguity

HOLD also appears inside ShareHOLDER

Review

Wildcard escaping

A literal * in COUNTIF requires a preceding tilde

Common

How to Excel IF Cell Contains Text

Practical workflow — 5 steps

Define the match

Decide whether you need a substring or standalone word, and whether letter case matters.

Choose SEARCH or FIND

Use SEARCH for case-insensitive matching and FIND when uppercase/lowercase must be exact.

Convert position to a test

Wrap SEARCH or FIND in ISNUMBER so matches return TRUE and non-matches return FALSE instead of #VALUE!.

Return or count

Wrap the test in IF to return a flag, or use COUNTIF with wildcards to count matching cells.

Check edge cases

Test blanks, mixed case, words embedded inside longer words, and literal wildcard characters before filling the formula down.

Verification rule: keep the source unchanged until the approved expected result, data types, row counts, and control checks agree with the workbook.

Excel IF Cell Contains Text — FAQs

How do I check if a cell contains specific text in Excel?

Use =ISNUMBER(SEARCH("text",A2)) for a case-insensitive substring check. Wrap it in IF when you need a label or other return value.

What is the difference between SEARCH and FIND in Excel?

SEARCH is not case-sensitive; FIND is case-sensitive. Both return the character position of a match and #VALUE! when no match exists.

How do I return a flag when a cell contains text?

Use a formula such as =IF(ISNUMBER(SEARCH("overdue",A2)),"Review","").

How do I count cells containing a word?

Use COUNTIF with surrounding wildcards, for example =COUNTIF(A2:A5,"*pending*"). COUNTIF text matching is not case-sensitive.

How do I search for a literal asterisk with COUNTIF?

Escape the wildcard with a tilde. A criterion such as "*~**" counts cells that contain an actual asterisk character.

Excel IF Cell Contains Text

How to Excel IF Cell Contains Text

Define the match

Choose SEARCH or FIND

Convert position to a test

Return or count

Check edge cases

Excel IF Cell Contains Text — FAQs

Related Excel workflows