[Solved] Conditional formatting on text in a text string

[ser_srd]

Hello all, hoping that someone can tell me if this is possible at all. I have not found a way to do this yet.

This is about conditional formatting, which I do know how to do, but not in the way I want to at the moment.

Here is what I want to do if possible:

I have a worksheet, and cell A1 has a long string of text in it. Let us say that this is the text:

SONIC3_FTR-2_F_EN-XX_US-GB_51-EN_CCAP_2K_ST_20070115_FAC_i3D_OV

If the text contains CCAP I want cell B1 background to turn green, but if it does NOT contain CCAP B1 background should go red or just stay auto, aka white.

I have done a lot of conditional formatting in the past, but never on text in a text string. BTW, there are never any spaces in these text strings.

Ideas appreciated.

[FJCC]

Set the condition to Formula Is and use the formula

Code: Select all

ISNUMBER(FIND("CCAP";A1))

[robleyd]

There is a discussion on this in this topic.

In summary, use a formula e.g.

Code: Select all

ISNUMBER(SEARCH("CCAP";A1;1))

aoo_cond.png (34.7 KiB) Viewed 4204 times

[ser_srd]

Wow, that was quick.

That worked great!

Thanks all.