Conditional Formatting & Audits
Highlight Cells That End With a Specific Suffix (RIGHT & LEN Exact Suffix Rule)
Flag filenames, SKUs, or email domains ending with a target suffix like ".csv" or "-TEMP" using TRIM, RIGHT, and LEN—avoiding wildcard false positives on "?" and "*".
Why RIGHT + LEN beats COUNTIF(A2, "*" & suffix) for suffix checks
Using COUNTIF(A2, "*" & $D$2) fails whenever a cell contains trailing spaces, exceeds 255 characters, or tests a suffix containing literal wildcard characters ("?" or "*").
Slicing RIGHT(TRIM(A2), LEN($D$2)) and comparing in lowercase with LOWER(...) = LOWER($D$2) checks exact character endings at any string length, ignores accidental trailing spaces, and handles punctuation literally.
| Cell Value | Target Suffix ($D$2) | RIGHT(TRIM(A2), LEN($D$2)) | Rule Result |
|---|---|---|---|
| "payroll_2026.CSV " | ".csv" | ".CSV" | TRUE (Case-insensitive) |
| "csv_backup.xlsx" | ".csv" | ".lsx" | FALSE (Prefix ignored) |
| "gl_export.csv" | ".csv" | ".csv" | TRUE |
| (Blank cell) | ".csv" | — | FALSE |
Case-Insensitive Suffix Conditional Formatting Formula
Guards against blank cells/suffixes, trims trailing spaces from A2, and compares the rightmost LEN($D$2) characters case-insensitively.
=AND(NOT(ISBLANK(A2)), LEN($D$2) > 0, LOWER(RIGHT(TRIM(A2), LEN($D$2))) = LOWER($D$2))Case-Sensitive Suffix Variant Using EXACT
Replace = with EXACT when "-TEMP" in uppercase must match while "-temp" in lowercase must not.
=AND(NOT(ISBLANK(A2)), LEN($D$2) > 0, EXACT(RIGHT(TRIM(A2), LEN($D$2)), $D$2))Try the example
Use the original values or change them to test your understanding.