Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Want a serious muslim match?Join our club and start searching now!!! | Excel Discussion (Misc queries) | |||
Want a serious muslim match?Join our club and start searching now!!! | Excel Worksheet Functions | |||
cell background color while searching through cells | Excel Discussion (Misc queries) | |||
Searching for first and last in a table to chart (gantt) | Excel Discussion (Misc queries) | |||
Searching for partial data in a column | Excel Discussion (Misc queries) |