excel : remove scientific notation

huiyin9218

Board Regular
Joined
Aug 7, 2018
Messages
53
Hi,

May I know is it possible to remove "E+02" from 3.840000E+02 to become 3.840000? I know the value is before and after is different but is it possible?

Hope you guys can help me.
 
Last edited:

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
You could use =10^(MOD(LOG(A1),1)) and format the cell appropriatly.

Or use =TEXT(10^(MOD(LOG(A1),1)), "0.000000")

or
=LEFT(TEXT(A1,"0.000000E+00"),8)
 
Last edited:
Upvote 0
remove "E+02" from 3.840000E+02
1. How is that cell currently formatted?

2. Assuming you have a column of these values, are they all ...E+02 or might some be ...E+05 etc? If there is variation in this regard, what should happen with those different values? Some more examples & expected results perhaps?
 
Upvote 0
1. How is that cell currently formatted?

2. Assuming you have a column of these values, are they all ...E+02 or might some be ...E+05 etc? If there is variation in this regard, what should happen with those different values? Some more examples & expected results perhaps?


The cells are in scientific format.
There are also other values (E+00, E+05, E-02 etc). The examples are as below,

2.744505E+002.744505
3.840000E+023.840000
5.132849E-015.132849

<tbody>
</tbody>

The values at the second column are the ones I hope to get.
 
Last edited:
Upvote 0
You could use =10^(MOD(LOG(A1),1)) and format the cell appropriatly.

Or use =TEXT(10^(MOD(LOG(A1),1)), "0.000000")

or
=LEFT(TEXT(A1,"0.000000E+00"),8)

It works! Thank you!
Is it possible to write it in code form? As I have over thousand values
 
Upvote 0
Perhaps
Code:
Sub test()
    Dim i As Long, xVal As Double, log10 As Double
    Dim arrInput As Variant, arrOutput As Variant
    log10 = Log(10)

    With Sheet1.Range("A:A")
        With Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
        
            arrInput = .Value
            arrOutput = arrInput
            
            For i = 1 To UBound(arrInput, 1)
                If IsNumeric(arrInput(i, 1)) Then
                    xVal = Val(arrInput(i, 1))
                    arrOutput(i, 1) = xVal / (10 ^ Int((Log(xVal) / log10)))
                End If
            Next i
            
            With .Offset(0, 1): Rem adjust
                .Value = arrOutput
                .NumberFormat = "0.000000"
            End With
        End With
    End With
End Sub
 
Last edited:
Upvote 0
Perhaps
Code:
Sub test()
    Dim i As Long, xVal As Double, log10 As Double
    Dim arrInput As Variant, arrOutput As Variant
    log10 = Log(10)

    With Sheet1.Range("A:A")
        With Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
        
            arrInput = .Value
            arrOutput = arrInput
            
            For i = 1 To UBound(arrInput, 1)
                If IsNumeric(arrInput(i, 1)) Then
                    xVal = Val(arrInput(i, 1))
                    arrOutput(i, 1) = xVal / (10 ^ Int((Log(xVal) / log10)))
                End If
            Next i
            
            With .Offset(0, 1): Rem adjust
                .Value = arrOutput
                .NumberFormat = "0.000000"
            End With
        End With
    End With
End Sub

It shows that there is invalid procedure call or argument at the row "arrOutput(i, 1) = xVal / (10 ^ Int((Log(xVal) / log10)))" :confused:
 
Upvote 0
Perhaps
Code:
Sub test()
    Dim i As Long, xVal As Double, log10 As Double
    Dim arrInput As Variant, arrOutput As Variant
    log10 = Log(10)

    With Sheet1.Range("A:A")
        With Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
        
            arrInput = .Value
            arrOutput = arrInput
            
            For i = 1 To UBound(arrInput, 1)
                If IsNumeric(arrInput(i, 1)) Then
                    xVal = Val(arrInput(i, 1))
                    arrOutput(i, 1) = xVal / (10 ^ Int((Log(xVal) / log10)))
                End If
            Next i
            
            With .Offset(0, 1): Rem adjust
                .Value = arrOutput
                .NumberFormat = "0.000000"
            End With
        End With
    End With
End Sub

Sorry sorry, the column beside it has values. Thats why it didnt work previously. It works now. Thank you so much. (y)
 
Upvote 0
You are right, this should protect against bad numbers

Code:
Sub test()
    Dim i As Long, xVal As Double, log10 As Double
    Dim arrInput As Variant, arrOutput As Variant
    log10 = Log(10)

    With Sheet1.Range("A:A")
        With Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
        
            arrInput = .Value
            arrOutput = arrInput
            
            For i = 1 To UBound(arrInput, 1)
                If IsNumeric(arrInput(i, 1)) Then
                  xVal = Val(arrInput(i, 1))
                  [COLOR="#FF0000"]If 0 < xVal Then[/COLOR]
                    arrOutput(i, 1) = xVal / (10 ^ Int((Log(xVal) / log10)))
                  [COLOR="#FF0000"]End If[/COLOR]
                End If
            Next i
            
            With .Offset(0, 1): Rem adjust
                .Value = arrOutput
                .NumberFormat = "0.000000"
            End With
        End With
    End With
End Sub
 
Last edited:
Upvote 0
It works now.
Must be no empty cells in the range then. :)

Mine assumes that cells in column A (starting at row 2 as I've assumed a heading row) are either empty of contain a numerical value in that scientific notation. Mine also puts the results in column B for now.
Code:
Sub RemoveIt()
  Dim c As Range

  For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
    c.Offset(, 1).Value = Left(c.Text, 8)
  Next c
  Columns("B").NumberFormat = "0.000000"
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

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