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]

Try

=A2-INT(A2)

 

[Joe4]

If you want to maintain leading zeroes and drop the 0 and decimal point so that you only have ALL the numbers after the decimal point, try this:

=MID(A2,FIND(".",A2)+1,LEN(A2))

 

[Ingolf]

Try:

=RIGHT(A2,LEN(A2)-FIND(".",A2))

 

[Legacy 143009]

First try to convert value to text:

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

 

[FormR]

There is always 4 digits after the decimal point

Hi, just another option.

Excel 2013/2016
	A	B	C
1	Mileage	Start Miles	Decimal Miles
2	30.0087	30	0087
3	59.6434	59	6434
4	113.057	113	0570
5	0.0085	0	0085
6	0.0712	0	0712
Sheet1

Cell Formulas
Range	Formula
C2	=RIGHT(TEXT(A2,".0000"),4)

 

[Chobits]

Hi, this is the one that worked for me but thank you for all of the replies. I wasn't expecting such a quick response and went to get lunch

Worksheet Formulas

<thead>
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]

</thead><tbody>
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=RIGHT( TEXT(A2,".0000"),4 )[/TD]

</tbody>

I think the reason I could not get the other suggestions to work is because having looked at the numbers in Column 1 in more detail, the data was 0.057 and it was the formatting of the cell to 4 decimal places that showed it at 0.0570. That's why it was dropping the 0 as in theory it never was there in the original entered data.

I've included this issue in this thread just in case anyone does a search on the same question (which I often do and often use this site for that reason).

 

[Gerald Higgins]

Sorry, I misread the OP.

If you want to display 4 digits after the decimal point, use my suggestion from post #2 , and use custom formating to display 4 digits, for example
0.0000

If you want to hide the decimal point, consider
=(A2-Int(A2))*100000
and again use a custom format, such as
0000

 

[Joe4]

I misread also, and my solution produced the results you showed in your grid.
Missed the part about the trailing zero.

 

[Chobits]

Hi Joe
I hadn't realised that the problem was mostly caused by the data formatting adding a '0' where there wasn't one, but it was needed as otherwise '057' in one cell and '0020' in another cell would have been treated as 57 and 20.
It was only when I looked at certain cells where 0 was the last number showing that I realised why various options were not working. The little things that catch you out!