Conditionally format numbers stored as text

Hi all,
I think this question really gets at a larger issue but here goes:

I'm doing a conditional format of a cell and I'm just comparing it to the cell next to it, and highlighting the cell if it's different than the one next to it. IE, in A1 the condition is <> A2.

It doesn't work...the issue seems to be this ongoing thing about what's a number and what's not. Cell A1 and A2 are both formatted as Text but *currently* contain only digits, ie, A1 = 1033 and A2 = 1033 but the A1 still gets highlighted.

I tested by changing the condition to <> INT(A2) and it works. Since these cells may contain alpha, I can't use INT() reliabley and I don't want to get into the hassle of using IF(), etc. within the format logic--I guess if I have to then I have to, but I get to a point and think "There's got to be an easier way".

Soooo...how do I get Excel to simply compare the cells as I have explicitly formatted them without writing a hundred lines of code in a formula that simply compares one cell to another? It's as if Excel is looking at numeric data as numeric--but only on one side of the comparison, it's not consistent in it's annoying tendencies.

What is the best way to get around this?
Thanks,
--Jim

Hi Jim,

How about this:

Please Login or Register  to view this content.

Cheers,

Can you clarify:

Originally Posted by jimojimo

highlighting the cell if it's different than the one next to it. IE, in A1 the condition is <> A2.

ie - next to it would imply A1<>B1 test rather than A1<>A2

Originally Posted by jimojimo

Cell A1 and A2 are both formatted as Text but *currently* contain only digits, ie, A1 = 1033 and A2 = 1033 but the A1 still gets highlighted.

This would not be, unless, one of the cells was formatted as Text after the values were first input.
Changing a cells format post data entry does not alter the value in any way - ie typing 10 into A1 and then formatting A1 as text does not make 10 into a text string (ie <> "10"... ISNUMBER remains True).
If you were to re-enter 10 then at that point the value would become a text string "10".

By the sounds of it A1 was entered pre formatting and A2 was not - ie A1 is a number and A2 is a text string.

Originally Posted by jimojimo

how do I get Excel to simply compare the cells as I have explicitly formatted them without writing a hundred lines of code in a formula that simply compares one cell to another?

Well, the above rather depends on whether you correct the original entries per the first point...
ie per your condition above and your original post A1 and A2 are different based on their format yet you seemingly want to contradict the logic rule and treat as identical.
I'm afraid you can't have it both ways so to speak - either they are different based on their format or they aren't (based on their coerced content) - and if the format is important you must re-enter your values as appropriate.

dll, I think the fundamental point remains though

Originally Posted by jimojimo

how do I get Excel to simply compare the cells as I have explicitly formatted them

ie if formatting is meant to be seen as means for differentiation normalising does not make a great deal of sense.

I suspect the reality is such that actually the format isn't that important (ie "10" and 10 should be treated as the same) and if so:

=NOT(EXACT(A1,B1))

might also suffice if case sensitivity is important (ie apple <> Apple, but "10" = 10)