ISNUMBER problem

[Mark]

I am trying to identify if the right character of a cell is numeric
and populate another cell based on the value

e.g. cell A1 contains the text G3

To me, it appears that the right character is 3. Therefore, if I set
up the following function in cell B1, I would expect a Y in the B1
cell :

=IF(ISNUMBER(RIGHT(A1,1)),"Y","N")

This is not the case. The IF statement is returning a FALSE value, and
thus, my B1 cell contains N.

As far as I can tell, the RIGHT function is returning the number 3 in
double quotes, and I think that these quotes are causing the problem
(although I may be wrong). How can I get this to work?

 

[Harlan Grove]

...
...

e.g. cell A1 contains the text G3

To me, it appears that the right character is 3. Therefore, if I set
up the following function in cell B1, I would expect a Y in the B1
cell :

=IF(ISNUMBER(RIGHT(A1,1)),"Y","N")

This is not the case. The IF statement is returning a FALSE value, and
thus, my B1 cell contains N.

As far as I can tell, the RIGHT function is returning the number 3 in
double quotes, and I think that these quotes are causing the problem
(although I may be wrong). How can I get this to work?

You're basically correct about the double quoets. They indicate that the numeral
'3' is treated as text. The binary representation of "3" is a string of ASCII
codes of length 1, specifically 51 decimal. Excel doesn't consider this a
number. If you enter just 3 into a cell, it doesn't appear within double
quotes, and it's stored as an 8-byte double precision floating point number. I'm
not going to hazard trying to represent the binary or hexadecimal representation
in memory, but it looks a lot different than the representation of the text "3".

Back to the point, if you want to test that a character is a decimal numeral,
use any of the following.

ISNUMBER(-RIGHT(A1,1))

ISNUMBER(VALUE(RIGHT(A1,1)))

(ABS(CODE(RIGHT(A1,1))-53.5)<5)

AND("0"<=RIGHT(A1,1),RIGHT(A1,1)<="9")

The first of these is the most efficient. Least typing too.