excel : remove scientific notation

[huiyin9218]

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.

 

[mikerickson]

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)

 

[Peter_SSs]

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?

 

[huiyin9218]

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,

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]2.744505E+00[/TD]
[TD]2.744505[/TD]
[/TR]
[TR]
[TD]3.840000E+02[/TD]
[TD]3.840000[/TD]
[/TR]
[TR]
[TD]5.132849E-01[/TD]
[TD]5.132849[/TD]
[/TR]
</tbody>[/TABLE]

The values at the second column are the ones I hope to get.

 

[huiyin9218]

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

 

[mikerickson]

Perhaps

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

 

[huiyin9218]

Perhaps

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)))"

 

[huiyin9218]

Perhaps

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.

 

[mikerickson]

You are right, this should protect against bad numbers

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

 

[Peter_SSs]

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.

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