extracting digits after decimal place

[jazflyer]

I'm trying to extract only the digits following a decimal place. The number of digits after the decimal place is variable up to 5

i.e. 1.2035 or 83.345 etc

I'm able to do it with the mid function but when the last digit after the decimal place is a zero it omits the zero. I need that zero.

i.e. 1.2030 returns 203 (I need 2030) or 1.0230 returns 023 etc.

Can anyone suggest a solution?

Thanks
Jaz

 

[hitch_hiker]

is there a maximun number of digits after ther decimal, could you add 0.00001 or 0.000000001 which will give you a finite number to then do your int() or right() or mid() would the addition of this affect your numbers?

 

[Firefly2012]

If you want to base the result on the actual formatted display of the cell, then you need to use VBA (so you can access the Text property of the cell). A suitable UDF could be:

Function GetMantissa(r As Range)
If IsNumeric(r.Value) Then 
   GetMantissa = Mid(r.Text,Instr(1,r.Text & ".",".")+1)
Else
  GetMantissa = vNullString
End If
End Function

To give output like:

Excel 2007
	C	D
11	1.234	234
12	1.340	340
13	1.000	000
14	5
15	1.56789	56789
16	1.0040	0040
17	3.23	23
Sheet3

Cell Formulas
Range	Formula
D11	=getmantissa(C11)

 

[jazflyer]

You seriously need to review your logic because it's going to come a cropper at some point. Based on what you said, what happens if you need to compare the decimals from:

1.025

and

1.25

How is the formula meant to know the second should return 250 or 25?

FF

I think it's best to show a picture of what I'm trying to achieve.

I want A-B to return the value in C (I've already accounted for negative values with ABS function)

As I said earlier, I'm able to get exactly what I want need with MID provided the number doesn't end in a zero like in rows 2 and 4 for example.

The answer may be quite obvious to some but it's got me stumped.

Thanks again

 

[jazflyer]

If you want to base the result on the actual formatted display of the cell, then you need to use VBA (so you can access the Text property of the cell). A suitable UDF could be:

Function GetMantissa(r As Range)
If IsNumeric(r.Value) Then 
   GetMantissa = Mid(r.Text,Instr(1,r.Text & ".",".")+1)
Else
  GetMantissa = vNullString
End If
End Function

To give output like:

Excel 2007<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>C</th><th>D</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">1.234</td><td style="text-align: right;;">234</td></tr><tr><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">1.340</td><td style="text-align: right;;">340</td></tr><tr><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">1.000</td><td style="text-align: right;;">000</td></tr><tr><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">5</td><td style=";">
</td></tr><tr><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">1.56789</td><td style="text-align: right;;">56789</td></tr><tr><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">1.0040</td><td style="text-align: right;;">0040</td></tr><tr><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">3.23</td><td style="text-align: right;;">23</td></tr></tbody></table>

Sheet3​

<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Worksheet Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px" width="100%"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">D11</th><td style="text-align:left">=getmantissa(C11)</td></tr></tbody></table></td></tr></tbody></table>

That appears to be exactly what I'm after..guess now I just have to familiarize myself with VBA.

Thanks

 

[Trevor G]

I would ask how are you showing the last digit as a zero if there are shown as number format? Is the format as text and you have aligned them in the column to the right?

 

[Firefly2012]

With your workbook open in Excel and visible in the Excel window, open up the Visual Basic Editor (with Alt+F11).

Once it has opened, insert a standard module with Alt+M. This will appear to the right. Paste the function code I gave you in there.

Now you can use the function in your sheet exactly like I have it in my post above.

 

[jazflyer]

I would ask how are you showing the last digit as a zero if there are shown as number format? Is the format as text and you have aligned them in the column to the right?

Trevor,

I've simply formatted the cell to show the appropriate amount of decimal places.

 

[jazflyer]

With your workbook open in Excel and visible in the Excel window, open up the Visual Basic Editor (with Alt+F11).

Once it has opened, insert a standard module with Alt+M. This will appear to the right. Paste the function code I gave you in there.

Now you can use the function in your sheet exactly like I have it in my post above.

Thanks FF...As I'm a complete noob to VBA I'll have to dig a bit to understand how this works. I appreciate everyone's help.

Jaz

 

[jazflyer]

With your workbook open in Excel and visible in the Excel window, open up the Visual Basic Editor (with Alt+F11).

Once it has opened, insert a standard module with Alt+M. This will appear to the right. Paste the function code I gave you in there.

Now you can use the function in your sheet exactly like I have it in my post above.

Thanks FF..got it! Works perfectly.

Mahalo
Jaz