Text & Regular Patterns
Count & List Which Keywords Appear Inside a Single Text Cell
Score support tickets, contract clauses, or incident notes by counting how many distinct keywords from a watchlist appear inside one cell and listing every matched keyword.
Score escalation severity from keyword hit counts and extract matched terms
Triage queues often score an incoming message by how many distinct risk keywords ("outage", "breach", "timeout", "refund") appear anywhere inside the note, then surface the exact matched terms for the reviewer.
Testing each non-blank keyword with ISNUMBER(SEARCH(keyword, noteCell)) returns TRUE (1) or FALSE (0) per watchlist term regardless of capitalization, and TEXTJOIN(", ", TRUE, IF(..., keyword, "")) assembles the matched keyword summary.
| Watchlist Keyword | Present in Incident Note? | Hit Score | Matched Summary |
|---|---|---|---|
| outage | Yes ("API outage...") | 1 | outage |
| breach | No | 0 | — |
| timeout | Yes ("...gateway timeout...") | 1 | timeout |
| refund | Yes ("...SLA refund request") | 1 | refund (Total = 3) |
Distinct Watchlist Keyword Hit Count in Cell B2
Counts how many non-blank watchlist terms in B4:B7 appear inside incident note B2.
=IF(ISBLANK(B2), 0, IF(ISBLANK(B4), 0, --ISNUMBER(SEARCH(B4, B2))) + IF(ISBLANK(B5), 0, --ISNUMBER(SEARCH(B5, B2))) + IF(ISBLANK(B6), 0, --ISNUMBER(SEARCH(B6, B2))) + IF(ISBLANK(B7), 0, --ISNUMBER(SEARCH(B7, B2))))Comma-Separated List of Matched Keywords
Lists which specific watchlist terms matched cell B2, or returns "No Keyword Hits" when zero match.
=LET(hits, TEXTJOIN(", ", TRUE, IF(ISNUMBER(SEARCH(B4, B2)), B4, ""), IF(ISNUMBER(SEARCH(B5, B2)), B5, ""), IF(ISNUMBER(SEARCH(B6, B2)), B6, ""), IF(ISNUMBER(SEARCH(B7, B2)), B7, "")), IF(LEN(hits) = 0, "No Keyword Hits", hits))Try the example
Use the original values or change them to test your understanding.