searching for cells that "contain" certain value
 

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

searching for cells that "contain" certain value
 

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,F 3))),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


"Giulia" wrote in message
...
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

searching for cells that "contain" certain value
 

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

"Bernie Deitrick" wrote:

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,F 3))),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


"Giulia" wrote in message
...
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

searching for cells that "contain" certain value
 

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

Bernie Deitrick wrote:

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,F 3))),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

"Giulia" wrote in message
...
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

--

Dave Peterson