If you want to highlight cells that end with certain text, you can use a simple formula based on the COUNTIF function. For example, if you want to highlight states in the range B4:G12 that end with “ota”, you can use: Note: with conditional formatting, it’s important that the formula be entered relative to the “active cell” in the selection, which is assumed to be B4 in this case. The formula itself uses the COUNTIF function to “count” cells that end with “ota” using the pattern “ota” which uses a wildcard () to match any sequence of characters followed by “ota”. From a practical standpoint, we are only counting 1 cell each time, which means we are either going to get back a 1 or a zero, which works perfectly for conditional formatting.
A simpler, more flexible rule using named ranges
By naming an input cell as a named range and referring to that name in the formula, you can make the formula more powerful and flexible. For example, if you name G2 “input”, you can rewrite the formula like so: This formula simply adds “*” to the beginning of whatever you put in the input cell. As a result, the conditional formatting rule will respond instantly whenever that value is changed.
Case sensitive option
COUNTIF is not case-sensitive, so if you need to check case as well, you can use a more complicated formula that relies on the RIGHT function together with EXACT: In this case, RIGHT extracts text from the right of each cell, and only the number of characters in the substring you are looking for, which is supplied by LEN. Finally EXACT compares the extracted text to the text you are looking for (the substring). EXACT is case-sensitive, so only will return TRUE when all characters match exactly.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
