Advanced Filter for Text String

[Daren]

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.

 

[Stefi]

Data>Autofilter>Choose Custom from drop down list>choose "contains" (last
option) Saturday
Repeat it for the other column!

Regards,
Stefi

„Daren†ezt írta:

 

[Bob Umlas]

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]

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]

In the meanwhile Bob gave a solution!
Stefi


„Daren†ezt írta:

 

[Daren]

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.

 

[Shane Devenshire]

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