I have also tried to get a formula to work on this but failed.

I found a simple one just to extract the decimal places.

=MOD(A1,1)

for 12.123 it give .123
for 12.1 it gives .1
Irrespective of size of decimal places.

Now just select the new column with formulas and change it to values. Copy, Paste special, values.

Select data, text to columns, delimiter="."

And you get what you want.

Hope this helps.

Aziz
PS: I know I am late (its been one year since it was asked).




Mike wrote:

extract decimal and convert to integer
19-Sep-08

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

EggHeadCafe - Software Developer Portal of Choice
WPF DataGrid Custom Paging and Sorting
http://www.eggheadcafe.com/tutorials...tom-pagin.aspx