| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced Filter for Text String
Hello,
I have text values in 2 columns of cells that contain the word Saturday in any length of values...e.g, The First Saturday, That Saturday was Nice. I want to be able to use an advanced filter to show any rows that contain the word Saturday in both columns. How can I do this assuming that Saturday can appear in columns D and E? Thanks. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Advanced Filter for Text String
DataAutofilterChoose Custom from drop down listchoose "contains" (last
option) Saturday Repeat it for the other column! Regards, Stefi €žDaren€ť ezt Ă*rta: Hello, I have text values in 2 columns of cells that contain the word Saturday in any length of values...e.g, The First Saturday, That Saturday was Nice. I want to be able to use an advanced filter to show any rows that contain the word Saturday in both columns. How can I do this assuming that Saturday can appear in columns D and E? Thanks. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Advanced Filter for Text String
Thanks, that worked for one of the columns, but when I did it for the other
column, the combined function acted as an "and" statement that only filtered rows that contained Saturday in both columns. I need the function to show all rows that contain Saturday in either column D or E. How can I do that? "Stefi" wrote: DataAutofilterChoose Custom from drop down listchoose "contains" (last option) Saturday Repeat it for the other column! Regards, Stefi €žDaren€ť ezt Ă*rta: Hello, I have text values in 2 columns of cells that contain the word Saturday in any length of values...e.g, The First Saturday, That Saturday was Nice. I want to be able to use an advanced filter to show any rows that contain the word Saturday in both columns. How can I do this assuming that Saturday can appear in columns D and E? Thanks. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Advanced Filter for Text String
In the meanwhile Bob gave a solution!
Stefi €žDaren€ť ezt Ă*rta: Thanks, that worked for one of the columns, but when I did it for the other column, the combined function acted as an "and" statement that only filtered rows that contained Saturday in both columns. I need the function to show all rows that contain Saturday in either column D or E. How can I do that? "Stefi" wrote: DataAutofilterChoose Custom from drop down listchoose "contains" (last option) Saturday Repeat it for the other column! Regards, Stefi €žDaren€ť ezt Ă*rta: Hello, I have text values in 2 columns of cells that contain the word Saturday in any length of values...e.g, The First Saturday, That Saturday was Nice. I want to be able to use an advanced filter to show any rows that contain the word Saturday in both columns. How can I do this assuming that Saturday can appear in columns D and E? Thanks. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Advanced Filter for Text String
If the column headers are in D1:E1, then somewhere where you have space, say
M1:M2, enter this formula in M2 leacing M1 blank: =OR(NOT(ISERROR(FIND("Saturday",D2))),NOT(ISERROR( FIND("Saturday",E2)))) then use M1:M2 as your criteria cells in the advanced filter dialog. Bob Umlas Excel MVP "Daren" wrote in message ... Hello, I have text values in 2 columns of cells that contain the word Saturday in any length of values...e.g, The First Saturday, That Saturday was Nice. I want to be able to use an advanced filter to show any rows that contain the word Saturday in both columns. How can I do this assuming that Saturday can appear in columns D and E? Thanks. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Advanced Filter for Text String
Thanks, but it did not seem to work properly. I ented the formula as you
said by inserting two spacer columns in F and G. Then I entered the formula like you stated below in column G and used that as the advanced filter criteria. The formula returned FALSE even when Saturday appeared in those cells. Then I broke the formula below into two separate columns where I would need to find the word Saturday, with a header row in D1, the formula =OR(NOT(ISERROR(FIND("Saturday",D6))) in cell D2. Then I entered another header row in E1 and the formula =OR(NOT(ISERROR(FIND("Saturday",E6))). When I used those as the criteria to search in the data set all rows were hidden. What can be the problems here? Thanks again. "Bob Umlas" wrote: If the column headers are in D1:E1, then somewhere where you have space, say M1:M2, enter this formula in M2 leacing M1 blank: =OR(NOT(ISERROR(FIND("Saturday",D2))),NOT(ISERROR( FIND("Saturday",E2)))) then use M1:M2 as your criteria cells in the advanced filter dialog. Bob Umlas Excel MVP "Daren" wrote in message ... Hello, I have text values in 2 columns of cells that contain the word Saturday in any length of values...e.g, The First Saturday, That Saturday was Nice. I want to be able to use an advanced filter to show any rows that contain the word Saturday in both columns. How can I do this assuming that Saturday can appear in columns D and E? Thanks. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Advanced Filter for Text String
Hi,
Try this Title1 Title2 *Saturday* *Saturday* Where Title1 and Title2 are the titles at the tops of your data columns F and G Note that the two conditions are on separate rows. For example the above is entered in A1:B3. Use this as the Criteria range for Advanced Filter -- If this helps, please click the Yes button Cheers, Shane Devenshire "Daren" wrote: Thanks, but it did not seem to work properly. I ented the formula as you said by inserting two spacer columns in F and G. Then I entered the formula like you stated below in column G and used that as the advanced filter criteria. The formula returned FALSE even when Saturday appeared in those cells. Then I broke the formula below into two separate columns where I would need to find the word Saturday, with a header row in D1, the formula =OR(NOT(ISERROR(FIND("Saturday",D6))) in cell D2. Then I entered another header row in E1 and the formula =OR(NOT(ISERROR(FIND("Saturday",E6))). When I used those as the criteria to search in the data set all rows were hidden. What can be the problems here? Thanks again. "Bob Umlas" wrote: If the column headers are in D1:E1, then somewhere where you have space, say M1:M2, enter this formula in M2 leacing M1 blank: =OR(NOT(ISERROR(FIND("Saturday",D2))),NOT(ISERROR( FIND("Saturday",E2)))) then use M1:M2 as your criteria cells in the advanced filter dialog. Bob Umlas Excel MVP "Daren" wrote in message ... Hello, I have text values in 2 columns of cells that contain the word Saturday in any length of values...e.g, The First Saturday, That Saturday was Nice. I want to be able to use an advanced filter to show any rows that contain the word Saturday in both columns. How can I do this assuming that Saturday can appear in columns D and E? Thanks. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| use list box text for advanced filter on dblClick | Excel Discussion (Misc queries) | |||
| Building criteria string for Advanced Filter variable not resolvin | Excel Discussion (Misc queries) | |||
| Need Advanced Filter with NOT equal string and OR criteria | Excel Discussion (Misc queries) | |||
| Advanced Filter using Date represented as text | Excel Worksheet Functions | |||
| advanced filter won't allow me to filter on bracketed text (-456.2 | Excel Discussion (Misc queries) | |||
Hybrid Mode
