3

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.

2
  • I recommend phrasing questions more carefully. You wrote "A through M" but then your example has letters a, b, c which are not in the range A-M. Also, "cell contains a letter A-M" could be understood as "the text in the cell contains a letter A-M", which would mean that "Argh" should match. – user79865 Sep 1 '15 at 21:10
  • Hey Norman - thanks. The "or" rule I was using worked for both lower and uppercase letters, so I got a little lazy with my precision of language. Your solution is much appreciated. – Aaron B. Sep 3 '15 at 13:30
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.