Replace decimal values with zero decimal value

Question

Lets say I have a number 2,65,563.56 in a particular cell. I want excel to automatically change it to 2,65,563.00. I could simply format the cell to display the integer part of the number only making the decimal part disappear but I want the decimal part to show as .00

Please note, I don't want excel to round off the number as it might alter the integer part of my number. Hence I would like excel to simply replace the non-zero decimal value (.56) with a zero decimal value (.00).

Thank you.

Answer 1

I believe FLOOR is the way, and you can specify the significance:

=FLOOR(value, 1)

Answer 2

There are several ways to do this. Aside from floor there is also:

=ROUNDOWN(value,0)

or

=INT(value)

or

=value-MOD(value,1)

or

=LEFT(A2,FIND(".",A2)-1)*1
=LEFT(A2,FIND(".",A2)-1)/1
=LEFT(A2,FIND(".",A2)-1)+0
=--LEFT(A2,FIND(".",A2)-1)
NOTE: the math operation at the end and the leading -- turns the text back into a number.  

If you do not need the value as a number useable in formulas you could also do:

=LEFT(A2,FIND(".",A2)-1)&".00"

Note: With the exception of the text option, you will have to adjust the format of the cells to display 2 decimal accuracy as under general cell format, leading and trailing zeros do not display. With the text option you may need to adjust cell justification to suit your needs.