Conditional format if value contains *Text* (with wildcards)

[Legacy 123760]

Hello. I'm trying to set up a conditional format so my cells will be filled if the value includes a specific text string, but I need to use wildcards before and after the specific text string. In other words, if the cell value contains the text "my specific text", anywhere in the cell value, I want to pick this up and use the conditional format. Nothing I've tried works, such as Cell Value Is - Equal to - "*my specific text*", or '*my specific text*'. Any help is appreciated. If this info is already posted somewhere on this site, forgive me, I've looked for quite a while but as usual, I really need this now. Thanks a bunch!

 

[Brian from Maui]

With Formula Is,

=SEARCH("TEXT",A1)

Where A1 is the cell of interest.​

 

[Legacy 123760]

Perfect!! Thank you, you are an angel!!!

 

[Legacy 123760]

Oh, I spoke too soon... It appeared to work, but the 'formula is' triggers the conditional formatting for every cell, because the cell itself uses an IF formula where the *text* is part of the value if IF is not true, which calculates the actual cell value based on several other things... so, the *text* is always part of the formula. Does that make sense?

 

[Brian from Maui]

Oh, I spoke too soon... It appeared to work, but the 'formula is' triggers the conditional formatting for every cell, because the cell itself uses an IF formula where the *text* is part of the value if IF is not true, which calculates the actual cell value based on several other things... so, the *text* is always part of the formula. Does that make sense?

Unless the formula returns "text", CF should not apply if the formula itself has the word text.

What is "based on several other things"?

 

[Legacy 123760]

This is the actual formula, with Sheet2!$B$7 being the piece of text that includes my desired *text* to trigger the CF. I changed the formula and the *text* now applies when ISNA=True. =IF(ISNA(INDEX(Sheet3!$A:$F,MATCH(C2,Sheet3!$A:$A,0),MATCH($E$1,$1:$1,0))),Sheet2!$B$7,(INDEX(Sheet3!$A:$F,MATCH(C2,Sheet3!$A:$A,0),MATCH($E$1,$1:$1,0))))