Custom Formats for Scientific Notation

=SUBSTITUTE(TEXT(A1,"0.0000000E+0"),"E","*10^") works better.

Chang e"0.0000000E+0" to whatever precision you need.

it's great. thank you so much

Here it is!!!!
All you have to do is place a # at the beginning of the regular scientific notation to format in engineering notation!!!

format: 0.000E+00 #00.000 E+00
------------------------------------
number: 3.142E+14 314.159E+12
3.142E+12 03.142E+12
3.142E+11 314.159E+09
3.142E+10 31.416E+09
3.142E+09 03.142E+09

Finally: a function for proper scientific notation!
For a very long time I have been looking for a function that converts a number to text in the real scientific format, not the clumsy notation with E+23 or 10^-5.
Finally I think I found a solution.
Rather than formatting part of the string into superscript, the function uses Unicode characters for superscript "0" to "9" and superscript "-".
The basic VBA function for the exponent part is given below. This can easily be extended to a function doing the whole number with automatic mantissa and exponent calculation.

Function SciExp(Expon As Integer) As String

Dim sExpon As String
Dim i As Integer
Dim e As String
Dim Dig As String
Dim U As Long

sExpon = CStr(Expon)
e = ""

If Expon = 0 Then
SciExp = ""
Exit Function
End If

For i = 1 To Len(sExpon)
Dig = CStr(Mid(sExpon, i, 1))

Select Case Dig
Case "0"
U = 8304
Case "1"
U = 185
Case "2"
U = 178
Case "3"
U = 179
Case "4"
U = 8308
Case "5"
U = 8309
Case "6"
U = 8310
Case "7"
U = 8311
Case "8"
U = 8312
Case "9"
U = 8313
Case "-"
U = 8315
End Select

e = e & ChrW(U)
Next i

SciExp = ChrW(183) & "10" & e
End Function

Hope this will help a lot of scientists out there...

Pieter de la Court