asked on

Basing conditional formatting on more than one text string

I am using the following formula in conditional formatting to change the cell in column AE to yellow when the criteria is met:

=AND(ISBLANK(AE2)=FALSE, ISNUMBER(SEARCH("DND",D2)))

I need to revise the formula to include an additional string of "MDN", so that the criteria is now:

If AE2 is not blank, and D2 has text string "DND" or "MDN", then change cell AE2 to yellow

I took a stab at trying to solve it myself with the following formula but it did not work for either "DND" or "MDN":

=AND(ISBLANK(AE2)=FALSE,IF(OR(ISNUMBER(SEARCH("DND",D2))),ISNUMBER(SEARCH("MDN",D2)))

Thanks!
Andrea

SOLUTION

Professor J

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Andreamary

ASKER

Both formulas work perfectly...thanks, folks! Since I'm forced to identify the 'best solution', and I'm not an expert, I made a choice based on the length of the formula, figuring a shorter formula is better, but please don't hesitate to correct me if this is an erroneous assumption.

Cheers,
Andrea

Subodh Tiwari (Neeraj)

You're welcome Andrea! Glad we could help.

Professor J

Andrea
while my IF condition was unnecessary, i have put it to portray a better picture otherwise it works like this =AND(ISBLANK(AE2)=FALSE,OR(ISNUMBER(SEARCH("DND",D2)),ISNUMBER(SEARCH("MDN",D2))))

but i prefer using the ISBLANK instead of nullstring "" becuase lets say if you have a formula in cell AE2 where the result is a double qoure (nullstring" in that case it <>"" then in that case the formula would not work.

so isblank is much preferable.

i am happy with split of points between me and my friend sktneer.

at the end what matter is that you get your solution, i do not really care much about points.

Andreamary

ASKER

Thank you, Professor Jim Jam, for your explanation...much appreciated!

Professor J

You are welcome