Andreamary

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

=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

Thanks!

Andrea

SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**
You're welcome Andrea! Glad we could help.

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.

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

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.

ASKER

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

You are welcome

ASKER

Cheers,

Andrea