extract decimal and convert to integer

[Mike H]

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

 

[Sheeloo]

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]

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

 

[Peo Sjoblom]

How about?

=--MID(TEXT(A1,"General"),FIND(".",TEXT(A1,"General"))+1,15)



--


Regards,


Peo Sjoblom

 

[Ron Rosenfeld]

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

 

[dlw]

This one has my brain box buzzing, can anyone come up with a formula? And
not using strings? (and by "strings" I mean text, not higher spatial
dimensions)

 

[Sheeloo]

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...

 

[Peo Sjoblom]

Oops! I missed the text fiddle. Can't be done without it though

--


Regards,


Peo Sjoblom

 

[Mike H]

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

 

[Abdul Aziz Farooq]

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 exampl
=MID(D1,3,LEN(D1))+
o
=(A1-(TRUNC(A1)))*10
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 poin
=($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 i

Mike

EggHeadCafe - Software Developer Portal of Choice
WPF DataGrid Custom Paging and Sorting
http://www.eggheadcafe.com/tutorial...f-32b2d802ae17/wpf-datagrid-custom-pagin.aspx