Solved: Convert exponential format to a number ?

[frank_m]

In Excel 2003 - How can I convert scientific Notation (exponential) to a number using a helper column and formula ?

7.9838E+11

In the past I've always used method (1) Shown below, but in this case that's not working.
(1) select range, Right click -> Format cells -> Number (0 decimal places)

(2)Right click -> Format cells -> Custom -> Typed in 11 zeros

If I choose Format cells -> Number (0 decimal places) then Press F2 and Enter, that works but only for one cell at a time.

Sample workbook attached,

Thanks

[geekgirlau]

Formula: =INT(P2)

[Kenneth Hobs]

For positive exponents, the =Int method will work fine.

You need to resize the column if you don't want Excel changing numbers to exponent notation.

For a VBA solution that converts strings for exponents, copy your strings and paste. With those selected cells run this. If you change the numberformat to 0, you will only see 0 for numbers less than 0.

Sub Expo()
Dim cell As Range, s() As String, lng As Long, n As Integer
For Each cell In Selection
With cell
If Not VarType(.Value2) = vbString Then GoTo NextCell
s() = Split(cell.Value2, "E")
.Value2 = s(0) * 1 * (1 * 10 ^ s(1)) 'ePart(s(1))
.NumberFormat = "General"
.EntireColumn.AutoFit
End With
NextCell:
Next cell
End Sub

[frank_m]

Formula: =INT(P2)
Thanks geekgirlau (http://www.vbaexpress.com/forum/member.php?u=450), that's what I was after :hi:


Thank you also Kenneth - Your VBA solution I'm sure I will need somewhere down the road :thumb