searching for cells that "contain" certain value

[Guest]

I am trying to filter several columns of a spreadsheet that contain specific
values on an OR basis, so I can't use the built in filters. I am using the
following function:

=IF(ISBLANK($A$1),"TRUE",(IF(OR(A3=$A$1,E3=$E$1,F3=$F$1),TRUE,FALSE)))

I can then filter one column which is either true or false. This is great
provided the values I am searching for are exact which they are not, does
anyone know how I may alter this function to return true or false if the cell
"contains" a specific value.

Cheers

 

[Bernie Deitrick]

Giulia,

Note: this is case sensitive:

=IF(ISBLANK($A$1),"TRUE",(IF(OR(ISNUMBER(FIND($A$1,A3)),ISNUMBER(FIND($E$1,E3)),ISNUMBER(FIND($F$1,F3))),TRUE,FALSE)))

IF you need it to be case insensitive, you could wrap the cell references in UPPER functions:

ISNUMBER(FIND(UPPER($A$1),UPPER(A3)))

HTH,
Bernie
MS Excel MVP

 

[Guest]

AWESOME ............ Thankyou

Giulia,

Note: this is case sensitive:

=IF(ISBLANK($A$1),"TRUE",(IF(OR(ISNUMBER(FIND($A$1,A3)),ISNUMBER(FIND($E$1,E3)),ISNUMBER(FIND($F$1,F3))),TRUE,FALSE)))

IF you need it to be case insensitive, you could wrap the cell references in UPPER functions:

ISNUMBER(FIND(UPPER($A$1),UPPER(A3)))

HTH,
Bernie
MS Excel MVP

 

[Dave Peterson]

or
isnumber(search(...))
to make it not case sensitive.