Codes & maths

[panyagak]

Hi Mrexcel Greats & Eric W.

Am wondering how to convert a long column with all manner of numbers to a non-zero digit: eg
210.66876468 to 2.1066876468
0.0486788676 to 4.86788676
11.694324507 to 1.1694324507
0.2516033212 to 2.516033212

Thanks

 

[Rick Rothstein]

=--LEFT(TEXT(A1,"0.00000000000000E+0"),16)

That is 14 zeros after the decimal point.

Bear in mind that there might be more decimal places than you see. For example, 0.0486788676 might be 0.0486788676123456. I presume that your intent is preserve all decimal places.

Also note that the actual original value might be 0.0486788676123456+1.2E-17, but the new value will be just 4.86788676123456. In other words, some unseen infinitesimal precision might be lost.

The only problem I see with your formula are the double minus signs in front.... assuming normat Excel numbers, a floating point number with more than 10 digits will be displayed with only 10 total digits with the last decimal digit being a rounded digit. If you omit the double minus, then all of the digits display (as a text number, of course). Also, here is another formula that will also display the same thing as your formula (with the double minus removed)...

=REPLACE(0+SUBSTITUTE(A1,".",""),2,0,".")

If the value that your original formula is outputs is required, my formulas can do that by adding the double minus sign to it.

 

[panyagak]

Rick Rothstein

Thank you for your contribution. Will apply all formulas & codes tomorrow & respond aptly.

Regards

 

[panyagak]

Joe4 & Joeu2004 & Rick Rothstein

Thank you ALL, YOUR SOLUTIONS GIVE ''SAME CORRECT ANSWERS

REGARDS

 

[panyagak]

Joe4 & thanks for your help.

Referring to your #10 code, suppose I need to select ANY RANGE of cells on which apply the formula, any tweak to your code?

I dont want to mess your code.

Thanks

 

[Rick Rothstein]

Joe4 & thanks for your help.

Referring to your #10 code, suppose I need to select ANY RANGE of cells on which apply the formula, any tweak to your code?

This is quite different from the code Joe4 posted for you, but it should return the result you want for a contiguous selection of cells all within a single column...

[table="width: 500"]
[tr]
	[td]Sub ConvertMacro()
  Selection = Evaluate("IF({1},REPLACE(0+SUBSTITUTE(" & Selection.Address & ",""."",""""),2,0,"".""))")
End Sub[/td]
[/tr]
[/table]

 

[Rick Rothstein]

This is quite different from the code Joe4 posted for you, but it should return the result you want for a contiguous selection of cells all within a single column...

[table="width: 500"]
[tr]
	[td]Sub ConvertMacro()
  Selection = Evaluate("IF({1},REPLACE(0+SUBSTITUTE(" & Selection.Address & ",""."",""""),2,0,"".""))")
End Sub[/td]
[/tr]
[/table]

The restriction I highlighted in red above does not actually apply to the code I posted... you can have a multi-column, multi-row selection and the code will still work as long as the selection is contiguous. One note about the above code... if you have any blank cells within your selection, the code, as written, will return a #VALUE ! error for them. The following modification of the above code will output the empty text string ("") for any blank cells within the selection...

Sub ConvertMacro()
  Selection = Evaluate("IF(" & Selection.Address & "="""","""",REPLACE(0+SUBSTITUTE(" & Selection.Address & ",""."",""""),2,0,"".""))")
End Sub

 

[panyagak]

Thanks Rick.

Before I test your codes, vmay be my emphasis is "ANY RANGE" NOT a column. Eg. G5:U3400, G2:Y1750......etc

With a column, a formula is straightforward.

May be extra Dims are needed to Joe4's code as opposed to "A1:A": am not sure how to proceed with that....

Regards

 

[panyagak]

<tbody>
[TD="class: xl67, colspan: 8"]Rick, others: (In applying the code in #16 )why would Y/X across be OK BUT the Y/X of TOTALS highlighted in GREEN is UNEQUAL
[/TD]

[TD="class: xl66"][/TD]
[TD="class: xl66"]X[/TD]
[TD="class: xl66"]Y[/TD]
[TD="class: xl66"]Y/X[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl66"][/TD]
[TD="class: xl66"]FORMER[/TD]
[TD="class: xl66"]LATTER[/TD]
[TD="class: xl66"]FORMER/LATTER[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67"][/TD]
[TD="class: xl68"]3.660000000[/TD]
[TD="class: xl68"]3.660000000[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]OK[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67"][/TD]
[TD="class: xl68"]0.374316940[/TD]
[TD="class: xl68"]3.743169399[/TD]
[TD="class: xl67"]10[/TD]
[TD="class: xl67"]OK[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67"][/TD]
[TD="class: xl68"]2.671532847[/TD]
[TD="class: xl68"]2.671532847[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]OK[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67"][/TD]
[TD="class: xl68"]0.640000000[/TD]
[TD="class: xl68"]6.400000000[/TD]
[TD="class: xl67"]10[/TD]
[TD="class: xl67"]OK[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67"][/TD]
[TD="class: xl68"]7.859375000[/TD]
[TD="class: xl68"]7.859375000[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]OK[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67"][/TD]
[TD="class: xl68"]0.127236581[/TD]
[TD="class: xl68"]1.272365805[/TD]
[TD="class: xl67"]10[/TD]
[TD="class: xl67"]OK[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67"][/TD]
[TD="class: xl68"]4.300000000[/TD]
[TD="class: xl68"]4.300000000[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]OK[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67"][/TD]
[TD="class: xl68"]1.318604651[/TD]
[TD="class: xl68"]1.318604651[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]OK[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67"][/TD]
[TD="class: xl68"]0.758377425[/TD]
[TD="class: xl68"]7.583774250[/TD]
[TD="class: xl67"]10[/TD]
[TD="class: xl67"]OK[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67"][/TD]
[TD="class: xl68"]3.168900000[/TD]
[TD="class: xl68"]3.168900000[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]OK[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67"][/TD]
[TD="class: xl68"]6.891100000[/TD]
[TD="class: xl68"]6.891100000[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]OK[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67"][/TD]
[TD="class: xl68"]1.221100000[/TD]
[TD="class: xl68"]1.221100000[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]OK[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67"][/TD]
[TD="class: xl68"]0.148900000[/TD]
[TD="class: xl68"]1.489000000[/TD]
[TD="class: xl67"]10[/TD]
[TD="class: xl67"]OK[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67"][/TD]
[TD="class: xl68"]1.861100000[/TD]
[TD="class: xl68"]1.861100000[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]OK[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67"][/TD]
[TD="class: xl68"]0.491100000[/TD]
[TD="class: xl68"]4.911000000[/TD]
[TD="class: xl67"]10[/TD]
[TD="class: xl67"]OK[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67"]SUM[/TD]
[TD="class: xl69"]35.491643443[/TD]
[TD="class: xl69"]58.351021952[/TD]
[TD="class: xl66"] 1.644077768 [/TD]
[TD="class: xl67"]OOPS!!![/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

</tbody>