I'd be very surprised if you see a response from MS. Every so often, an MS
employee will post a response to a question--but they're few and far between.

For the overwhelming most part, this is a peer to peer newsgroup--just regular
people trying to help regular people <bg.

Frag wrote:

Hi Dave

Thanks for your post. I'm not explicitly trying to find 0 or any number -
rather I'm trying to match the text in the cell (even tho' it is a hex
number)

I think that excel is seeing your text as numbers--some in scientific notation

Exactly! And I think that it shouldn't be doing this if I have told it to
format the cells as text. As I said it only seems to happen for 0e00 - 0e19
(and 0 of course).

Another bit of proof: if against the range in my example, in a spare cell
you say =COUNTIF(A1:A20,"0") the result will be 2 if both A1 = 0000 and
A15=0e00.

I would be interested if MS have a reply to this.
Frag

"Dave Peterson" wrote:

When you work with =countif() or =sumif(), you'll notice that you can use
strings in that criteria argument.

=countif(a1:a20,""&0)
to count the number of cells in A1:A20 that are greater than 0.

I think that excel is seeing your text as numbers--some in scientific notation

If you want to distinguish between a number 0 and text '0 (apostrophe for
emphasis only), you can use a different function:

=SUMPRODUCT(--($A$1:$A$20=B1))0



Frag wrote:

I have two sets of numbers in hex and am comparing one set against the other.
It appears that 0E00 when entered as a text formatted cell is treated as
numeric 0 when consequently referenced for formatting operations.

(The problem actually happens with certain ranges 0E00-0E09 but not 0E0A -
0E0F, and then again with 0E10-0E19 but not 0E1A-0E1F etc)

I have tried setting the format of cells used for the ranges to text before
entering the values and using the apostrophe before each value, and
combinations of both techniques - I still get the same issue.

I can supply an example if required. Any ideas?

Thanks

--

Dave Peterson


--

Dave Peterson