• Home
 · 
• Miscellaneous
 · 
• Format the number style (##,##,###.# etc...)

Format the number style (##,##,###.# etc...)

Format the number style (##,##,###.# etc...)

API Declarations


'You can remove leading zeros by setting bNoLeadZeros = True else (by Default)
'You can remove trailing zeros after decimal point by setting bNoTrailZeros = True (by Default)



Rate Format the number style (##,##,###.# etc...)

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

    'Variables...
    Dim iPoint, sTemp, sNum, iLen
    Dim l, sSource

    'Startup...
    FormatNum = "0"

    'If not a number then exit...
    If Not IsNumeric(sNumber) Then Exit Function

    'Remove leading zeros, if demanded...
    If bNoLeadZeros Then
        Do
            sTemp = Left(sNumber, 1)
            If sTemp = "0" Then sNumber = Mid$(sNumber, 2) Else Exit Do
        Loop
    End If

    'Remove trailing zeros after decimal, if demanded...
    If bNoTrailZeros And (InStr(sNumber, ".") > 0) Then
        Do
            sTemp = Right(sNumber, 1)
            If sTemp = "0" Then sNumber = Left$(sNumber, Len(sNumber) - 1) Else Exit Do
        Loop
    End If

    'Remove the decimal point, if it is at last and nothing is after it...
    If Right(sNumber, 1) = "." Then sNumber = Left(sNumber, Len(sNumber) - 1)

    'Remove the pre-defined commas, if any...
    sNumber = Replace(sNumber, ",", "")

    'Value-up the variables...
    iPoint = InStr(sNumber, ".")
    iLen = IIf((iPoint = 0), Len(sNumber), iPoint - 1)

    'Conditional execution for given number...
    If (iLen < 6 And iLen > 3) Then
        If iPoint = 0 Then
            sNum = Left(sNumber, Len(sNumber) - 3) & "," & Right(sNumber, 3)
        Else
            sNum = Left(sNumber, iPoint - 1)
            sNum = Left(sNum, Len(sNum) - 3) & "," & Right(sNum, 3) & Mid(sNum, iPoint + 1)
            sNum = sNum & "." & Mid$(sNumber, iPoint + 1)
        End If
        FormatNum = sNum
        Exit Function
    ElseIf (iLen < 4 And iLen > 0) Then
        FormatNum = sNumber
        Exit Function
    ElseIf iLen = 0 And iPoint > 0 Then
        FormatNum = "0." & Mid$(sNumber, iPoint + 1)
        Exit Function
    ElseIf iLen = 0 Then
        Exit Function
    End If

    'Have commas "," after two digits...
    sSource = StrReverse(Left$(sNumber, iLen - 3))
    'sNum = Empty
    For l = 1 To Len(sSource) Step 2
        sNum = sNum & "," & Mid(sSource, l, 2)
    Next l
    If l <> Len(sSource) Then sNum = sNum & "," & Mid(sSource, l)
    If Right(sNum, 1) = "," Then sNum = Left(sNum, Len(sNum) - 1)

    'Finalise output...
    sNum = StrReverse(Mid$(sNum, 2)) & "," & Mid$(sNumber, Len(sSource) + 1)

    'Done...
    FormatNum = sNum
End Function
'Please tell me about Bugs, comments, feedbacks, etc, if any...