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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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