Conditional formatting on embedded word

[agibsonsw]

Hello. Excel 2003.
I would like to apply conditional formatting to a column where the cell's text contains a word.
Is it possible to do this with a wildcard character ="*some*" or do I need to use a function FIND or SEARCH
as part of a formula?

Thanks, Andy.

[Don Wells]

Hello. Excel 2003.
I would like to apply conditional formatting to a column where the cell's text contains a word.
Is it possible to do this with a wildcard character ="*some*" or do I need to use a function FIND or SEARCH
as part of a formula?

Thanks, Andy.

Assuming the the column of interest is "A".

• Select the entire column

• Pull down 'Format > Conditional Formatting'

• Select 'Formula Is'

• Enter the following formula

    =IF(ISERR(FIND("some",A1)),0,FIND("some",A1))
Or simply:
    =FIND("some",A1)

[rory]

If you don't want case-sensitivity, then use SEARCH rather than FIND, or use COUNTIF with wildcards.

[agibsonsw]

Hello. Thanks both.
So wildcard characters only apply to COUNTIF, SUMIF, etc., and auto-filtering within Excel? Andy.

[HansV]

Wildcards definitely do NOT work in a formula such as ="*some*"; the = operator looks for an exact match.