CODEWITHSUNDEEP
google-sheetsgs-conditional-formattinggoogle-sheets-formula
Nazzarus
Nazzarus

Reputation: 21

How to apply conditional formating to a list of strings?

I have a sheet full of strings, columns A:AH There are 16 different options for the cells to equal. I want to color them in groups of 4

So say, 

=or(match("Apples",A:AH),match("Bananas",A:AH),match("Pears",A:AH),match("Plums",A:AH))

except that doesn't work. I can always go in and add 16 text equals conditionals, just trying to find a more elegant solution.

Upvotes: 0

Views: 618

Answers (2)

CalamitousCode
CalamitousCode

Reputation: 1414

Using REGEXMATCH is another option.

Apply the custom formula condition below to the range A:AH.

=REGEXMATCH(A1,"(?i)^(apples|bananas|pear|plumb)$")

Code explanation

(?i) this adds the ignore-case flag: match Apples, apples, APPLES, apPPles, etc

| The pipe means OR

The two symbols below ensure that the cell contains ONLY the specified string.

^ Beginning of the string

$ End of the string

Plain English explanation

  • Ignore the case
  • Start at the beginning
  • Find apples, bananas, pear, or plumb
  • Make sure the end of the string is next
  • Returns TRUE or FALSE

Check out regex101.com for a great testing tool.

Upvotes: 1

player0
player0

Reputation: 1

=IF(OR(A1="apples", 
       A1="bananas", 
       A1="pear", 
       A1="plumb"), 1)

enter image description here

Upvotes: 1

Related Questions