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

 

[Joe4]

Here is one way (for an entry in cell A1):

=(LEFT(SUBSTITUTE(A1,".","")+0,1) & "." & MID(SUBSTITUTE(A1,".","")+0,2,LEN(A1)))+0

 

[njimack]

Here's a VBA approach...

Function Math(c As Range)
Math = c.Value
If Math > 1 Then
    Do Until Int(Math) < 10
    Math = Math / 10
    Loop
End If
If Math < 1 Then
    Do Until Int(Math) > 1
    Math = Math * 10
    Loop
End If
    
End Function

 

[Joe4]

Just be aware that loops are typically slow and inefficient, so if you have a lot of data, you could be doing a ton of loops, which could affect performance.
Also, just be aware that User Defined Functions are not volatile, by default. See here for an explanation of what that means: https://fastexcel.wordpress.com/201...10-volatile-functions-and-function-arguments/

 

[joeu2004]

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

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

 

[Joe4]

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

Slick!

I was thinking that there has to be a way to do it making use of the leading numbers in exponential notation, but couldn't quite figure out how to pull it off.

 

[panyagak]

Joe4: njimack: joeu2004

Thanks ALL.....I thought my post would only be VIEWED!!!!

njimack - hope you address Joe4's concerns on "Loops in UDFs" for efficiency. Data set is large

Let me COMPREHENSIVELY test your approaches from a PC tomorrow on a long column of numbers: my mobile device is inefficient.

Thanks again

 

[joeu2004]

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

If A1 might be negative:

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

 

[panyagak]

joeu2004

ALL are positive values.

thanks

 

[Joe4]

If you can get away with just using formulas, I would recommend joeu2004's code from post #5 .

If you want a VBA approach so you can simply replace the current value, you can apply his logic to VBA code, something like this:

Sub ConvertMacro()

    Dim lastRow As Long
    Dim cell As Range

    Application.ScreenUpdating = False
        
'   Find last row in column A
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all cells in column A, starting in column A
    For Each cell In Range("A1:A" & lastRow)
        cell = Left(Format(cell, "0.00000000000000E+0"), 16) * 1
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub

Note that this approach does (also) use a loop (to update each value), but it is only one loop iteration per cell, as opposed to an undetermined number of iterations per cell.

If you are pasting the result to a different cell (instead of over top of itself), you could get away with no loops. But if that was the case, there would be no need for VBA, just use Joe's formula.