I am assuming you know how Excel stores floating point numbers. IF not then
pl. reade http://www.cpearson.com/excel/rounding.htm.

Since there is no way to determine the no. of places after decimal any
number has in the internal storage, it is not possible to get what you are
trying.

You can try to get the LEN of the decimal part but it usually returns 17 but
is not reliable...

"Mike H" wrote:

Hi,

Thanks for that but it's not generic. You have to manually decide to
multiply by 100 which is fine for nnn.25 but isn't for nnn.256 for that you
must Multiply by 1000.

That has been the tricky bit for me working out in the formula what to
multiply by.

Mike

"Sheeloo" wrote:

Suppose the number is in A1
Then
=ROUND(A1,0) will give you the integer part
= (A1 - ROUND(A1,0)) will give you the decimal part
Multiply this by 100 and round again as in step 1

You can use ROUNDDOWN() if you do not want rounding instead of ROUND()
Let me if this is what you were looking for

"Mike H" wrote:

Hi,

Yesterday a question was posted in which the OP wanted to take the number
101.25 and extract the decimal 0.25 and convert that into an integer 25.

A one off solution is simple, for example
=MID(D1,3,LEN(D1))+0
or
=(A1-(TRUNC(A1)))*100
and of course a modulus/multiplication solution.

But none of these are generic for longer decimals so I set out to find a
generic solution for any number length.

This works perfectly for 101.25 and for any number to the left of the
decimal point
=($A$1-TRUNC($A$1))*(10^(LEN(($A$1-TRUNC($A$1)))-2))

But add any extra decimals and it can fail. For example 100.256 works
perfectly because (i think) it must have a precise decimal/binary conversion
but 101.257 falls over because the decimal portion is actually
0.257000000000005 so my formula that raises the (number*10^length of decimal
bit) fails.

I am missing something blindingly obvious so can anyone help me with a
mathmatical (not a text fiddle) to this problem that will convert any number
irrespective of the number of decimals. I haven't tried 'precision as
displayed' because I instinctively don't like it

Mike