Run formula if text is found in a cell.

[Timberwolf]

Good day all

I'm trying to add a clause to a formula. What I want is to check cell L2 and only if it finds the word "open" then run the following formula. Any help is greatly appreciated. TIA

=IF(ISNUMBER(SEARCH("Not used",E2)),"",IF(TODAY()=E2,"Today",IF(TODAY()>E2,"Past Due",IF((TODAY()+7)>E2,"Due Soon",""))))

 

[igold]

Is this what you are looking for...

=IF(L2="open",IF(ISNUMBER(SEARCH("Not used",E2)),"",IF(TODAY()=E2,"Today",IF(TODAY()>E2,"Past Due",IF((TODAY()+7)>E2,"Due Soon","")))),"")

 

[Yongle]

=IF(L2="open",IF(ISNUMBER(SEARCH("Not used",E2)),"",IF(TODAY()=E2,"Today",IF(TODAY()>E2,"Past Due",IF((TODAY()+7)>E2,"Due Soon","")))),"")


Edit
Beaten to the punch

 

[Eric W]

In case that "open" might be part of a longer text value:

=IF(COUNTIF(L2,"*open*"),IF(ISNUMBER(SEARCH("Not used",E2)),"",IF(TODAY()=E2,"Today",IF(TODAY()>E2,"Past Due",IF((TODAY()+7)>E2,"Due Soon","")))),"")

 

[igold]

@Yongle

It was the red highlighting that did you in.

 

[Timberwolf]

..... Sometimes you look for a huge answer when it really is that simple....... Thanks All, worked great!

 

[Yongle]

@igold - you are correct