excel string comparison is failing when it should not

Question

When I do =RIGHT(DATA!$A$2:$A$1501,1) I get the value 3. When I do =$B$5 I get the value 3. When I do =RIGHT(DATA!$A$2:$A$1501,1)=$B$5 I get the value FALSE.

How the f%^k can 3 not equal 3. Is this a data type issue? Do I need a cast? If so how?

Answer 1

Yes, that's probably a data type issue. Try

=RIGHT(DATA!$A$2:$A$1501,1)=TEXT($B$5,"general")

via: The JLD Excel Blog - String to number, Number to String

Answer 2

One of your "3"s is probably text. Try this:

=VALUE(RIGHT(DATA!$A$2:$A$1501,1))=VALUE($B$5)