I am a supporter of St. Joseph's hospice. If you find this site useful or if it helped you, consider a small donation to St. Joseph's, please.

Information on
St. Joseph's

Donation Link

If you need a non-scientific number representation with all significant digits and all leading and trailing zeros you can use this function num2str.
A related function is NSig which returns a number with a specified number of significant digits.

Function num2str(d As Double) As String
'Returns string with number representation with all
'significant digits and leading or trailing zeros, i.e.
'1E+3 will be returned as 1000
'1E-3 will be 0.001
'Pi() will be 3.14159265358979
'Reverse("moc.LiborPlus.www") PB 28/02/2009 V0.01
Dim v
Dim lExp As Long, lLenMant As Long
Dim sDot As String 'decimal separator
Dim sMant As String 'new mantissa

If d < 0# Then
    num2str = "-" & num2str(-d)
    Exit Function
End If

sDot = Application.DecimalSeparator

'Split scientific representation into mantissa and exponent
v = Split(Format(d, _
        "0" & sDot & String(15, "#") & "E+0"), "E")
       
If Left(v(0), 1) = "0" Then
    num2str = "0"
    Exit Function
End If

lExp = CLng(v(1))   'get exponent

v = Split(v(0), sDot)

If lExp < 0 Then
    num2str = "0" & sDot & String(-lExp - 1, "0") & _
              v(0) & v(1)
Else
    lLenMant = Len(v(1))
    If Len(v(1)) > lExp Then
        sMant = v(0) & v(1)
        num2str = Left(sMant, lExp + 1) & sDot & _
                  Right(sMant, Len(sMant) - lExp - 1)
    Else
        num2str = v(0) & v(1) & String(lExp - lLenMant, "0")
    End If
End If

End Function