Text, Cleaning & Extraction
Conditional Formatting If Cell Contains Specific Text, Any Text, or Not Equal
Write bullet-proof text conditional-formatting formulas: =ISNUMBER(SEARCH("urgent", $B2)) for case-insensitive substring highlights, =ISNUMBER(FIND("URGENT", $B2)) for exact-case tokens, and =AND(ISTEXT($B2), LEN(TRIM($B2))>0) for non-blank text.
Why SEARCH vs. FIND vs. ISTEXT solves every text-based Conditional Formatting rule!
Built-in "Text that Contains..." presets break down the moment you need to highlight an ENTIRE row, match EITHER of two keywords ("Urgent" OR "Escalated"), enforce case sensitivity, or ignore cells containing only spaces! Custom formula rules solve all four cleanly:
Use =ISNUMBER(SEARCH("urgent", $B2)) for case-insensitive substring matching (guarded against empty keyword cells via NOT(ISBLANK($E$2))) or =ISNUMBER(FIND("ERR-", $B2)) for case-sensitive error codes! To highlight cells containing ANY visible text (excluding numbers, blanks, and whitespace-only strings), use =AND(ISTEXT($B2), LEN(TRIM($B2)) > 0); and for Not-Equal rules that ignore blank rows, use =AND(NOT(ISBLANK($C2)), TRIM($C2) <> "Approved")!
| Text Formatting Rule Goal | Custom Formula (Top-Left Row 2) | Key Advantage Over Preset |
|---|---|---|
| Contains Substring (Case-Insensitive) | =ISNUMBER(SEARCH("urgent", $B2)) | Highlights entire row A2:D2 when $B2 is column-locked |
| Contains EITHER Keyword ("urgent" OR "sev1") | =OR(ISNUMBER(SEARCH("urgent", $B2)), ISNUMBER(SEARCH("sev1", $B2))) | Single rule instead of stacked duplicate rules |
| Contains Visible Text (Ignores Numbers & Spaces) | =AND(ISTEXT($B2), LEN(TRIM($B2)) > 0) | Rejects numeric IDs and accidental spacebar cells |
| Not Equal to "Approved" (Ignores Blank Rows) | =AND(NOT(ISBLANK($C2)), TRIM($C2) <> "Approved") | Prevents blank template rows from lighting up! |
Multi-Keyword Row Highlight Rule (Contains "urgent" OR "sev1", Case-Insensitive)
Returns TRUE when ticket note $B2 contains either "urgent" or "sev1" regardless of letter case.
=OR(ISNUMBER(SEARCH("urgent", $B2)), ISNUMBER(SEARCH("sev1", $B2)))Blank-Safe "Not Equal to Approved" Rule =AND(NOT(ISBLANK($C2)), TRIM($C2) <> "Approved")
Flags non-approved status rows while keeping blank unsubmitted rows unhighlighted.
=AND(NOT(ISBLANK($C2)), LEN(TRIM($C2 & "")) > 0, TRIM($C2 & "") <> "Approved")Try the example
Use the original values or change them to test your understanding.