Advanced Filter, Does Not Contain

Posted on

QUESTION :

I have a very long column of three-character combinations of alphanumeric and dash characters. Below is a very small sample of the data.

![enter image description here

How do you filter this column, so that any cell or combination that contains a number or dash is removed or hidden?

I have tried looking everywhere online for a solution, but cannot find anything other than it has something to do with Excel’s Advanced Filter.

Any help would be greatly appreciated.

ANSWER :

You can use the Advanced Filter with a formula criteria.

If your list starts in A4 (with the data start in A5), then the formula would be:

=MIN(FIND({0,1,2,3,4,5,6,7,8,9,"-"},A5&"0123456789-"))>LEN(A5)

This will return TRUE if digits or the dash are not found in the cell

Suggest you read about using Formulas for criteria in the Advanced Filter Help material. Note that the formula criteria cannot be in row 1 of your criteria range. In row 1 you must have a label that is NOT the same as any of your column labels, or it can be blank.

Before

enter image description here

Filter Dialog

enter image description here

After

enter image description here

Leave a Reply

Your email address will not be published. Required fields are marked *