RIGHT function doesn't read 0's

  • Thread starter Thread starter nikita
  • Start date Start date
N

nikita

If I use RIGHT,2 on a cell with the value 30.00, it's output is 3, not
0. What's going on here?
 
Hi,

If the cell is formatted a number with 2 decomal places then the output of

=RIGHT(A1,2)

Should be 30

The reason can be seen if you select the cell and look in the formula bar,
the .00 bit is a format that you see but the real content og the cell is 30.
Enter 30.01 and the same formula will output 01 because now the .01 bit is
'real' and not a format.


Mike
 
nikita said:
If I use RIGHT,2 on a cell with the value 30.00,
it's output is 3, not 0.
Click to expand...

Use RIGHT(TEXT(A1,"0.00"),2).

What's going on here?
Click to expand...

With RIGHT(A1,2), if A1 is a numeric, it is really difficult to know what
you will get. For example, put 1E-16 into A1 and look RIGHT(A1,2). Then
put 1E-28 into A1.

It seems that in that form, the RIGHT result depends on how the numeric
value appears in the Formula Bar; and that is unspecified, AFAIK. (Although
we might be able to reverse-engineer the rules.
 
what the cell contains and what you see depends upon whatever formattign you
have, when you use a STRING function on a cell containiong a number you'll
get these 'strange' results
fro example , type 30 into any unformatted cell, then format to 2 dps. Nowin
the immediate window type
?right(selection.text,5)
I get
0.00 whe I'd have expected to see 30.00
... there's a trailing space. so specifically format numbers before you test
them with string functions is the rule!
 
Back
Top