extract decimal and convert to integer
 

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

extract decimal and convert to integer
 

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

extract decimal and convert to integer
 

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

extract decimal and convert to integer
 

How about?

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



--


Regards,


Peo Sjoblom

"Mike H" wrote in message
...
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

extract decimal and convert to integer
 

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

extract decimal and convert to integer
 

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)

"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

extract decimal and convert to integer
 

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

extract decimal and convert to integer
 

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

--


Regards,


Peo Sjoblom

"Peo Sjoblom" wrote in message
...
How about?

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



--


Regards,


Peo Sjoblom

"Mike H" wrote in message
...
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

extract decimal and convert to integer
 

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

Your query
 

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