Does Not Equal

[Orgelizer]

I've encountered this problem both with Excel 2003 and 2007. Attempting the
same thing in Access works fine. I'd sure appreciate it if someone could
tell me why it doesn't work in Excel and does in Access.

Specifically, I have a spreadsheet where data in all but the date column is
formatted as text. That means "John", W3B29AWV", and "0502" are all text
fields. When I do either an AutoFilter or an Advanced Filter, if I look for
a specific thing, the proper results are returned. Using a "does not equal"
in AutoFilter, or <> in Advanced Filter works most of the time. However, if
I select a "does not equal" out of the AutoFilter, or use <> as criteria in
the Advanced filter, for the field that contains all numbers (0502 in this
example), the criteria is totally ignored. What is it about a "text" field
that is entirely numbers that the programs don't like.

As I've said, I've generated a query in Access and successfully had it work,
but it doesn't in Excel. Any info you could provide, either as to why it
doesn't work, or how to make it work, would be appreciated.

Thanks in advance for any and all assistance.

 

[Pete_UK]

If you try to type 0502 into a cell which is formatted as general,
then Excel will display it as 502. What happens when you press the
Enter key after typing it is that Excel tries to make sense of what
you have typed. It sees that you have typed just digits, and so
concludes that you want to enter a number. Numbers do not normally get
shown with leading zeros (though you can change this by formatting the
cell to do so), and so Excel strips the leading zero and converts what
you have typed into a number. (This is actually a simplistic
description of the parser).

A similar thing happens when you are using filters - once you have
clicked OK in the custom filter box, Excel has to try to make sense of
what you have set up. So, if you have chosen "Not equal to" a text
value made up entirely of numbers, Excel scans this, can split it into
two parts (the comparison operation and the operand), then looks at
the operand and sees that it is just made up of digits and concludes
that this must represent a number. Hence you do not get any matches
with your textual numbers in that column. Text values are stored
internally as a series of Ascii codes representing the characters in
the string, whereas numeric values are stored as binary numbers -
hence they can't be compared directly.

The parser is just a bit of the underlying Excel software - that is
how it has been programmed, and we can't change it. We just have to
learn these limitations or restrictions (and then work out ways of
getting it to do what we really want to achieve).

Hope this helps.

Pete

 

[Glenn]

I've encountered this problem both with Excel 2003 and 2007. Attempting the
same thing in Access works fine. I'd sure appreciate it if someone could
tell me why it doesn't work in Excel and does in Access.

Specifically, I have a spreadsheet where data in all but the date column is
formatted as text. That means "John", W3B29AWV", and "0502" are all text
fields. When I do either an AutoFilter or an Advanced Filter, if I look for
a specific thing, the proper results are returned. Using a "does not equal"
in AutoFilter, or <> in Advanced Filter works most of the time. However, if
I select a "does not equal" out of the AutoFilter, or use <> as criteria in
the Advanced filter, for the field that contains all numbers (0502 in this
example), the criteria is totally ignored. What is it about a "text" field
that is entirely numbers that the programs don't like.

As I've said, I've generated a query in Access and successfully had it work,
but it doesn't in Excel. Any info you could provide, either as to why it
doesn't work, or how to make it work, would be appreciated.

Thanks in advance for any and all assistance.

You could try "does not contain" instead of "does not equal". That seems to
work properly (or maybe that should be phrased "the way you want it to") with
leading zeros.

Of course, that could lead to other problems, because 0502 and 305023 both
contain 0502 and you may not want to filter out 305023.