Exclude text with advanced filter

  • Thread starter Thread starter ken smith
  • Start date Start date
K

ken smith

I have a list with values such as:

Buy
Sell
Settle Buy-Child
Settle Sell-Child
Settle Buy
Settle Buy Cancel

I need to filter for those rows containing "Settle" but
excluding those containing "Child". Lots of other criteria
too so autofilter no good. What is the syntax for "not
containing the string 'Child'"?

TIA,Ken
 
One way, assume your list is in A and the first
value (not header) is in A2. Put this formula in
let's say F2 (leave F1 blank)

=AND(ISNUMBER(FIND("Settle",A2)),ISERR(FIND("Child",A2)))

I would probably copy to another location and then as criteria range use

$F$1:$F$2

from your example it will return

Settle Buy
Settle Buy Cancel

So with a formula always refer to the first data value in your list
 
Thanks, nice trick with FIND. Is there a way to do this
without adding another column to the list? In other words
putting something into the criteria range of the advanced
filter of the column containing the values which would
have the opposite effect of putting "*child*" without the
quotes.
 
Back
Top