Apologies for the late response, I had to do some work!!

Thanks for all the responses. I think if I ever did have to solve this as
opposed to a purely academic exercise I'd go with the text solutions offered
by Peo & Ron but remain of the view that it could be done using math but am
happy to accept it's beyond me.

At least I understand now why nn.128, nn.256 etc work for my solution it
because they do have a precise binary conversion and yes Thanks Sheeloo I do
know how Excel stores numbers.

Mike H

"Ron Rosenfeld" wrote:

On Fri, 19 Sep 2008 11:28:01 -0700, 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

I don't think you can do it using worksheet functions without doing a "text
fiddle", do, at least in part, to the reason you mention.

But try:

=REPLACE(A1,1,FIND(".",A1),"")

or

=--REPLACE(A1,1,FIND(".",A1),"")

to do this using a "text fiddle".

--ron