Ask Your Question

What is the symbol (or command) for "Does Not Contain" in Advanced Filtering? [closed]

asked 2014-07-05 18:05:44 +0200

satupe gravatar image

updated 2014-07-06 09:46:41 +0200

bencomp gravatar image

I probably did not ask the question correctly, but this is my dilemma. I have a macro that generates "Advanced Filtering" commands. The <>, <=, >= symbols work great. BUT, suppose I wish to filter out all instances of "Chet Atkins" from my music database. When I use the <>Chet Atkins option, the advanced filter removes "Chet Atkins", but "Chet Atkins & Jerry Reed" (etc) remains. My question is how do I remove all instances of "Chet Atkins" without adding "<> Chet Atkins & Jerry Reed" (and all other instances of "Chet Atkins") to the advanced filter? Is there a symbol for "Does Not Contain"?

This link show my current Advanced Filtering setup =>


edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-28 20:22:28.598275

1 Answer

Sort by » oldest newest most voted

answered 2014-07-05 22:19:45 +0200

Regina gravatar image

updated 2014-07-05 23:14:11 +0200

Find the option "Search criteria = and <> must apply to whole cells" in Tools > Options > Calc > Calculate. If it is not checked, then =Chet Atkins will find records too, where the text "Chet Atkins" is only part of the cell content, and <>Chet Atkins will find the complement of them.

Or you enable the option "Regular expressions" in the filter definition and search for ^(?!.*Chet Atkins).*$. (?!.*Chet Atkins).* seems to work too. Look for technical term "negative look-ahead".

But I don't know, how to set these options using a macro.

edit flag offensive delete link more


Thank you - works great!!! I'll place your info in the instructions since I cannot find any means of setting "Options" via a macro:-) Extracting mp3's from a 15,000 file music library to a 16 gig flash drive for our cars has been a fun project for an old retiree. I love Chet Atkins but my daughter-in-law doesn't:-)

satupe gravatar imagesatupe ( 2014-07-07 13:55:53 +0200 )edit

Question Tools

1 follower


Asked: 2014-07-05 18:05:44 +0200

Seen: 2,430 times

Last updated: Jul 05 '14