sbNum2Str

If you need a non-scientific number representation with all significant digits and all leading and trailing zeros you can use this function sbNum2Str.
A related function is sbNSig which returns a number with a specified number of significant digits.
Please read my Disclaimer.
Function sbNum2Str(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 15-Nov-2010 V0.20
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
    sbNum2Str = "-" & sbNum2Str(-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
    sbNum2Str = "0"
    Exit Function
End If
​
lExp = CLng(v(1))   'get exponent
​
v = Split(v(0), sDot)
​
If lExp < 0 Then
    sbNum2Str = "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)
        sbNum2Str = Left(sMant, lExp + 1) & sDot & _
                  Right(sMant, Len(sMant) - lExp - 1)
    Else
        sbNum2Str = v(0) & v(1) & String(lExp - lLenMant, "0")
    End If
End If
​
End Function

sbNSig

sbORB

Last modified 2yr ago

Copy link