Codes & maths

panyagak

Active Member
Joined
Feb 24, 2017
Messages
299
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Here is one way (for an entry in cell A1):
Code:
=(LEFT(SUBSTITUTE(A1,".","")+0,1) & "." & MID(SUBSTITUTE(A1,".","")+0,2,LEN(A1)))+0
 
Upvote 0
Here's a VBA approach...

Code:
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
 
Upvote 0
Upvote 0
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.
 
Last edited:
Upvote 0
Solution
=--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.
 
Upvote 0
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
 
Upvote 0
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:
Code:
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.
 
Upvote 0

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top