extracting digits after decimal place

[jazflyer]

I'm trying to extract only the digits following a decimal place. The number of digits after the decimal place is variable up to 5

i.e. 1.2035 or 83.345 etc

I'm able to do it with the mid function but when the last digit after the decimal place is a zero it omits the zero. I need that zero.

i.e. 1.2030 returns 203 (I need 2030) or 1.0230 returns 023 etc.

Can anyone suggest a solution?

Thanks
Jaz

 

[Firefly2012]

Hi Jaz

Do you want it in Text format and always 4 digits?

Perhaps:

=TEXT(MOD(A1,1)*10000,"0000")

 

[Robert Mika]

What about:
=VALUE(MID(A1,3,5)*10)

 

[jazflyer]

Hey firefly

I just need to exact numbers after the decimal place. I need to turn the value of the decimals into an integer so that I can subtract a similar number. For example
1.2030 - 1.2010 = 20

or

82.25 - 82.10 = 15

If all the values had the same decimal place I'd just multiply by the appropriate number but the decimal values differ.

Thanks

 

[Trevor G]

Would this work for you.

Excel Workbook
	A	B
2	23.3409	3409
3	21.09209	09209
Sheet3

 

[Firefly2012]

From what you've stated, it seems to me that you just need to multily by 10000 (assuming you have a max of 4 digits after the decimal place).

=MOD(A1,1)*10000

 

[jazflyer]

Would this work for you.

Aloha Trevor,

Using 1.0850 as an example, your suggestion still only returns a value with the zero omitted. (085)



Thanks

 

[jazflyer]

From what you've stated, it seems to me that you just need to multily by 10000 (assuming you have a max of 4 digits after the decimal place).

=MOD(A1,1)*10000

FF

Because of the variable decimal places ie .xxxx or .xxx etc, simply multiplying by 1000 or 10000 doesn't seem to work.

For example 82.87 should return 87 (not 8700) just as 1.360 should return 360

Thanks

 

[Firefly2012]

You seriously need to review your logic because it's going to come a cropper at some point. Based on what you said, what happens if you need to compare the decimals from:

1.025

and

1.25

How is the formula meant to know the second should return 250 or 25?

 

[Trevor G]

Aloha Trevor,

Using 1.0850 as an example, your suggestion still only returns a value with the zero omitted. (085)



Thanks

From your statement above this means you are needing to use text for the cell value as you wouldn't see the 1.0850 if it was numeric? So my formula would be correct if they are formatted as numbers or as text.