Sulprobil
Search…
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
Last modified 2yr ago
Copy link