Search code examples
excelcriteriaadvanced-filter

Excel Advanced Filter Criteria - Starts with, does not end with

As a VERY simplified example of a huge set of rules I'm dealing with, suppose I have a range in Excel with, say, the following column:

Col1
ABCD
ABCE
ABCF
ABCG
BCDE

And I would like to create an advanced filter that says Begins with ABC, but does not include ABCD or ABCE.

I tried do create the advanced autofilter as follows:

Col1     Col1
ABC*     <>ABCD*
ABC*     <>ABCE*

But the first rule lets in ABCE and the second rule ABCD, so the filter does not work as I'd want.

Using a tool such as regular expressions, I could write it as ABC[^DE], but I'm a bit lost if there's a way to create this with Excel and autofilters specifically.

Again, this is a very simplified example, but that is my general challenge.

Any ideas?

Thanks!

Solution

  • Try these 3 functions(the criteria header must be included as blank):

    enter image description here

    E2:=LEFT(A2,3)="ABC" F2:=RIGHT(A2)<>"D" G2:=RIGHT(A2)<>"E"