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

When I format a cell as text and type in 0E00, it stays as a text string.
What exactly are you doing? How are you putting the data into the cells?
What operation are you carrying out on the data?
--
David Biddulph

"Frag" wrote in message
...
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

Hi David

It's not changing the format type, that is ok. I did this:
In Columns A + B, cells 1-20 formatted as text, enter the values
0000
0100
0200
0300
0400
0500
0600
0700
0800
0900
0a00
0b00
0c00
0d00
0e00
0f00
1000
1100
1200
1300

In cell B1 apply Conditional Formatting
Formula Is = COUNTIF(A1:A20,B1) format Pattern "Red"
then apply that to the rest of the values in column B

At this point, all of those cells should be red...

If you "Clear Contents" of, say, A3 then B3 should go unhighlighted. This
works for any cell except A1 (0000) or A15 (0e00).

Clearing A1 does not unformat B1, but unclearing A15 clears B1 and B15.
Seeing as 0e00 as a 'normal' number is 0 is what makes me think Excel is
behaving strangely. Either that or my formula is not clever enough!

Frag

"David Biddulph" wrote:

When I format a cell as text and type in 0E00, it stays as a text string.
What exactly are you doing? How are you putting the data into the cells?
What operation are you carrying out on the data?
--
David Biddulph

"Frag" wrote in message
...
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

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

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

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