Getting Numbers after a decimal point

[Chobits]

Hi All,
I have a question around getting numbers after a decimal point.
The data with the numbers are in column A.

I've used =INT(A2) to get the number in front of the decimal point in full (as it ranges from 0 to 120) and this works fine.

The issue I am having is getting the number after the decimal point in full.
I've tried using =MID(A2,FIND(".",A2)+1,5) which mostly works but if there is a 0 at the end it drops it.

For example
in row 4, the formula in C4 return 057 instead of 0570.
Below is an example of the data.

Can anyone suggest a way to stop it dropping the last digit if a 0 please?
There is always 4 digits after the decimal point but using the =RIGHT formula does not work as it returns .057 instead.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Mileage (A1)[/TD]
[TD]Start Miles (B1)[/TD]
[TD]Decimal Miles (C1)[/TD]
[/TR]
[TR]
[TD]30.0087[/TD]
[TD]30[/TD]
[TD]0087[/TD]
[/TR]
[TR]
[TD]59.6434[/TD]
[TD]59[/TD]
[TD]6434[/TD]
[/TR]
[TR]
[TD]113.0570[/TD]
[TD]113[/TD]
[TD]057[/TD]
[/TR]
[TR]
[TD]0.0085[/TD]
[TD]0[/TD]
[TD]0085[/TD]
[/TR]
[TR]
[TD]0.0712[/TD]
[TD]0[/TD]
[TD]0712[/TD]
[/TR]
</tbody>[/TABLE]

 

[Gerald Higgins]

Many of the solutions posted here seem to involve converting the digits after the decimal place to a text string.

I personally wouldn't do that, as it can make it more difficult to carry out mathematical functions on the results.
It's not impossible, but it can be more fiddly.

Maybe you don't want to do any calculations on the results - maybe you just want to display the values, in which case a text string should be fine.

But if you do want to calculations on the results, consider an option that preserves the underlying values as numbers, not text strings, and then consider formating tricks to modify the way they appear.
This is what my suggestions do.

 

[Legacy 143009]

First try to convert value to text:

=MID(TEXT(A4,"0,0000"),FIND(".",TEXT(A4,"0.0000"))+1,5)

Sorry my formula didn't work because of regional settings. The first comma will be a point.

=MID(TEXT(A4,"0.0000"),FIND(".",TEXT(A4,"0.0000"))+1,5)

 

[Rick Rothstein]

If all of the number are formatted to 4 decimal places and none of the numbers have more than 4 decimal places in them, then I think this formula should work...

=RIGHT(10000*A1,4)

which will return the value as a text string. To get a real number result, simply add 0 to the above formula...

=0+RIGHT(10000*A1,4)

I believe another possibility for getting a real number result is this...

=MOD(10000*A1,10000)