Advanced Filter - filter rows <>

[Guest]

Why does using the <> operator work for filter criteria one column but not in
another? It does not filter out the rows in my "Store Number" column.

Example: The following does not work...

Store
<>4

This works...
Location
<>Outdoor

 

[Roger Govier]

Hi

Maybe your store numbers are really text
Try
Store
<>"4"

 

[Guest]

I have tried text and number formats. If my value is formatted as a number
then <>4 should return all row except for those with 4. Here is what I do
know...

I added a character to my number #4 and it filters fine. The not equal
operator is not filtering a number regarless of the format. It does seem to
work with the other operators.

 

[Roger Govier]

Hi

There is no inherent reason why <>4 should not work. It works fine on
all applications where I use Advanced Filter.
I would still suspect the data in the Store column.
In a separate column on that sheet, enter =LEN(A2) and copy down, having
substituted A2 with the cell number of your first store value.
Does the result come to 1 for stores with a supposed value of 4?
If not there may be leading or trailing spaces (Char(32), or the
non-breaking space character (char(160).
If you find the length is greater than expected, try entering in another
column
=SUBSTITUTE(SUBSTITUTE(A2,Char(160),"")," ","")
and copy down.
Copy this new data and Paste Special>Values back over the data in your
Store column and see if the advanced filter works then.