Formula that Does NOT Contain 2 Different Texts

Question

So I'm using Google sheets and I would like a cell to conditionally highlight when the cell does not have 2 specific texts.

So in my example, I would like cell B26 to highlight ONLY if it does not contain NYS or JFK.

Is that possible? It doesn't seem like there's a built in option in Google sheets.

A lot of guides only teach how to create custom formulae for texts that exist.
e.g., =ISNUMBER(SEARCH("JFK",B26))

I'm looking for the opposite effect.

Daniele · Accepted Answer · 2022-05-15 15:14:10Z

1

In Conditional Formatting try with a custom formula like this:

=and (B8:B <> "JFK", B8:B <> "NYS" , B8:B <> "JFK/NYS" )

answered May 13 at 16:53

Daniele

7131 gold badge2 silver badges9 bronze badges

Don't think this is right. Try =not(and (B8 <> "JFK", B8 <> "NYS" , B8 <> "JFK/NYS" ) )
– Tedinoz
May 15 at 9:55
@Tedinoz Maybe you read the question too quickly ... your formula will highlight ALL cells containing JFK / NYS ... but OP wanted exactly the opposite i.e. all but those
– Daniele
May 15 at 15:00
You are zbsolutely right, I had left formula as-is, but my data was in Colum A. Argh.
– Tedinoz
May 15 at 23:48
This worked! Thank you so much!!!
– user295402
May 19 at 8:27
So I tried the same thing for column C (but I replaced JFK & NYS with "1 Aug 2022" and "31 July 2022", and "B8:B to "C8:C"), changing the Apply to range field to C8 - but somehow it doesn't work. Is there a reason for this?
– user295402
May 19 at 14:20

| Show 1 more comment

Cypherjac · Accepted Answer · 2022-05-13 13:51:58Z

1

Have you tried checking the options under Conditional Formatting

You can add multiple rules to a single cell and apply the rules to other cells afterwards

answered May 13 at 13:51

Cypherjac

1364 bronze badges

What would you enter in the "Value or formula field"?
– Tedinoz
May 15 at 9:43
Tried this at first. Basically if I do this, the cell is highlighted if it doesn't have both NYS and JFK.
– user295402
May 19 at 8:10

Add a comment |

2 Answers 2