CODEWITHSUNDEEP
google-sheetsgs-conditional-formatting
HelloWorld
HelloWorld

Reputation: 21

Value contains many text in Conditional Format Rules Google Sheet

I want to set conditional format rules in my Google spreadsheet. For the format cell, I select "Text contains", then I type values "PA, MA, CT, NY", formatting style I choose red. When I click DONE, the columns containing these words didn't show the color. I don't want to create rules "PA', "MA", "CT"and "NY" one by one. How can I fix them? Thanks for helping.

Upvotes: 1

Views: 15063

Answers (3)

pnuts
pnuts

Reputation: 59442

Select the relevant range (I am assuming starts at A1) and clear any existing CF rules from it. Format, Conditional formatting..., Format cells if... Custom formula is and:

=regexmatch(A1,"PA|MA|CT|NY")

with red fill and Done.

This should format any cells that contain any of the four state abbreviations (that is, both as part of the content of a cell and as all the content of the cell). It should format PACT but being case sensitive not many.

Upvotes: 3

James D
James D

Reputation: 3142

As Ed said, but to clarify,

Go to conditional format and select 'Custom formula'

Assuming that your values are in column A, set the range to A1:A and enter the formula =OR($A:A="PA",$A:A="MA",$A:A="CT",$A:A="NY") in the custom formula field.

If the values are not in column A then change the formula as necessary.

Upvotes: 0

Ed Nelson
Ed Nelson

Reputation: 10259

Try using OR with custom formula like this:

=or($A2="PA",$A2="MA",$A2="CT",$A2="NY")

Upvotes: 0

Related Questions