searching for cells that "contain" certain value

[Giulia]

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


"Giulia" <Giulia@discussions.microsoft.com> wrote in message
news:07E94CC9-4C59-4C35-A162-F830C007838D@microsoft.com...
>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

[Giulia]

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,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
>
>
> "Giulia" <Giulia@discussions.microsoft.com> wrote in message
> news:07E94CC9-4C59-4C35-A162-F830C007838D@microsoft.com...
> >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]

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,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
>
> "Giulia" <Giulia@discussions.microsoft.com> wrote in message
> news:07E94CC9-4C59-4C35-A162-F830C007838D@microsoft.com...
> >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