Google Sheets – Specify a Range of Text for Conditional Formatting

conditional formattinggoogle sheets

I would like Google Sheets to look at a column and highlight a cell red if it contains any of the letters A through M. My current solution is using a Custom Formula that looks like this =or(D4="a",D4="b",D4="c", and so on).

I'm wondering if anyone can think of a way to do it without so many arguments. To somehow specify the range of letters A through M.

Best Answer

I know, I'll use regular expressions.

Conditional formatting (say, applied to the block A1:Z) with custom formula

=regexmatch(A1,"^[A-M]$")

highlights the cells with a single upper case letter from A to M. Similarly, the formula

=regexmatch(A1,"^[a-m]$")

matches lowercase a-m, and

=regexmatch(A1,"^[A-Ma-m]$")

matches either case.

Explanation

  • ^ asserts position at the beginning of the string
  • [...] requires exactly one character from the specified character group
  • $ asserts position at the end of the string

Without ^ and $, the expression would match any string containing at least one character from the specified group.

Related Topic