Right function with second decimal place is 0

t0azt

Board Regular
Joined
Aug 1, 2012
Messages
134
How do I get the right function to pick up 0 as the second decimal place?

I'm trying to get zero from =RIGHT(A1) where A1 is 2,366,125.60 but excel is picking up the 6 since it drops the last zero.

Thanks
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Maybe the format of the cell has 2 decimals.
That's why you can not get zero,
Test with 123.45 should result 5
 
Upvote 0
It works with all decimals except 0. The cells are formatted to include 2 decimal places.
so 123.45 will give me 5 but 123.40 will give me 4 and I need 0
 
Upvote 0
Excel does not pay any attention to cell format when you reference a numeric cell in a formula. In fact, if the value in A1 were 123.543 formatted to display 2 decimal places (appears to be 123.54), RIGHT(A1) would return 3.

If you always want the 2nd decimal place (rounded), write RIGHT(TEXT(A1,"0.00")).

However, if you always want the right-most displayed digit (character), I think you will need to use a VBA function. For example:

Function myRight(c As Range, n As Long) As Variant
myRight = Right(c(1).Text, n)
End Function
 
Last edited:
Upvote 0
We all know that after the point the zeros to the right do not work.

Then let there be zero

=RIGHT(TEXT(A1,".00"),1)
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top