Value = 7534721.45
Typical format with grouping of three digits: 7,534,721.45
Indian format with grouping of two-three digits: 75,34,721.45
Format(Value, ";-") & Format(Abs(Fix(Value)), Right("##\,##\,##\,##\,##\,##\,", ((Len(CStr(Abs(Fix(Value)))) - 2) \ 2) * 4) & "##0") & IIf(Value - Fix(Value), LTrim(Str(Abs(Value - Fix(Value)))), "")
Format(Value, ";-") & Format(Abs(Fix(Value)), Right("##\,##\,##\,##\,##\,##\,", ((Len(CStr(Abs(Fix(Value)))) - 2) \ 2) * 4) & "##0") & IIf(Value - Fix(Value), Left(LTrim(Str(Abs(Value - Fix(Value)))) & "0000", Sgn(Digits) + Digits), Mid(".0000", 2 - Sgn(Digits), Sgn(Digits) + Digits))
' Format a Currency value in the Indian number format.
'
' Value can be any value between the minimum and maximum of Currency:
' Value = CCur("-922337203685477.5808")
' Value = CCur(" 922337203685477.5807")
'
' Example:
' 922337203685477.5807 -> "92,23,37,20,36,85,477.5807"
'
' Digits controls the decimal count:
' If Digits is less than 0, floating decimals are used.
' If Digits is between 0 and 4, fixed decimals are used.
'
' Note:
' For fixed decimals less than four, parameter Value
' should be rounded before passed to this function.
'
' Source:
' https://en.wikipedia.org/wiki/Indian_numbering_system
'
' 2021-01-31. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function FormatIndianCurrency( _
ByVal Value As Currency, _
Optional ByVal Digits As Integer = -1) _
As String
Const MaxDecimals As Integer = 4
Dim TextValue As String
If Digits < 0 Then
' Floating decimal.
TextValue = _
Format(Value, ";-") & _
Format(Abs(Fix(Value)), _
Right("##\,##\,##\,##\,##\,##\,", _
((Len(CStr(Abs(Fix(Value)))) - 2) \ 2) * 4) & _
"##0") & _
IIf(Value - Fix(Value), LTrim(Str(Abs(Value - Fix(Value)))), "")
Else
' Fixed decimal.
If Digits > MaxDecimals Then
Digits = MaxDecimals
End If
TextValue = _
Format(Value, ";-") & _
Format(Abs(Fix(Value)), _
Right("##\,##\,##\,##\,##\,##\,", _
((Len(CStr(Abs(Fix(Value)))) - 2) \ 2) * 4) & _
"##0") & _
IIf(Value - Fix(Value), _
Left(LTrim(Str(Abs(Value - Fix(Value)))) & "0000", Sgn(Digits) + Digits), _
Mid(".0000", 2 - Sgn(Digits), Sgn(Digits) + Digits))
End If
FormatIndianCurrency = TextValue
End Function
Value = -387.89
Format(Value, ";-") -> "-"
Value = -387.89
Abs(Fix(Value)) -> 387
"##\,##\,##\,##\,##\,##\,##0"
"##\,##\,##\,##\,##\,##\,##0"
"##\,##\,##\,##\,##\,##0"
"##\,##\,##\,##\,##0"
"##\,##\,##\,##0"
"##\,##\,##0"
"##\,##0"
"##0"
((Len(CStr(Abs(Fix(Value)))) - 2) \ 2) * 4
Right("##\,##\,##\,##\,##\,##\,", ((Len(CStr(Abs(Fix(Value)))) - 2) \ 2) * 4)
Right("##\,##\,##\,##\,##\,##\,", ((Len(CStr(Abs(Fix(Value)))) - 2) \ 2) * 4) & "##0"
Value = 11.345
Str(Value - Fix(Value)) -> " .345"
Value = -11.345
Str(Value - Fix(Value)) -> "-.345"
Value = -11.345
Str(Abs(Value - Fix(Value))) -> " .345"
Value = -11.345
LTrim(Str(Abs(Value - Fix(Value)))) -> ".345"
Str(0) -> " 0"
IIf(Value - Fix(Value), LTrim(Str(Abs(Value - Fix(Value)))), "")
If you wish fixed decimals of, say, two decimals, an extended expression for the decimal part must be applied, and the requested count of decimals (digits) be specified. If the decimal part is not zero, the decimal value must be adjusted to match the count of digits. Problem is, that the decimal separator must be included if a decimal value is present, thus the sequence of the count of digits 0, 1, 2, 3, 4 shall return a string length of 0, 2, 3, 4, 5. The function Sgn is ideal for this, at it returns 1 for one or more digits and 0 (zero) otherwise; when adding this to the digit count value, the correct string length is obtained. In the first section, Left is used to cut off the length of decimals having a value:Note: The method described here will not perform rounding, only cut off exceeding decimals or fill with zeroes for missing decimals. If the value must be rounded, do it before formatting it. The simple method for 4/5 rounding is to use Format:Value = 7344.628 RoundedValue = CCur(Format(Value, "0.00")) RoundedValue -> 7344.63
For any other rounding where accuracy is important, avoid the buggy Round and use the functions found at VBA.Round.
Left(LTrim(Str(Abs(Value - Fix(Value)))) & "0000", Sgn(Digits) + Digits)
Mid(".0000", 2 - Sgn(Digits), Sgn(Digits) + Digits))
IIf(Value - Fix(Value), Left(LTrim(Str(Abs(Value - Fix(Value)))) & "0000", Sgn(Digits) + Digits), Mid(".0000", 2 - Sgn(Digits), Sgn(Digits) + Digits))
Positive value | Positive value formatted | Negative value | Negative value formatted |
---|---|---|---|
922337203685477.5807 | 92,23,37,20,36,85,477.5807 | -922337203685477.5808 | -92,23,37,20,36,85,477.5808 |
92233720368547.7581 | 9,22,33,72,03,68,547.7581 | -92233720368547.7581 | -9,22,33,72,03,68,547.7581 |
9223372036854.7758 | 92,23,37,20,36,854.7758 | -9223372036854.7758 | -92,23,37,20,36,854.7758 |
922337203685.4776 | 9,22,33,72,03,685.4776 | -922337203685.4776 | -9,22,33,72,03,685.4776 |
92233720368.5478 | 92,23,37,20,368.5478 | -92233720368.5478 | -92,23,37,20,368.5478 |
9223372036.8548 | 9,22,33,72,036.8548 | -9223372036.8548 | -9,22,33,72,036.8548 |
922337203.6855 | 92,23,37,203.6855 | -922337203.6855 | -92,23,37,203.6855 |
92233720.3685 | 9,22,33,720.3685 | -92233720.3685 | -9,22,33,720.3685 |
9223372.0369 | 92,23,372.0369 | -9223372.0369 | -92,23,372.0369 |
922337.2037 | 9,22,337.2037 | -922337.2037 | -9,22,337.2037 |
92233.7204 | 92,233.7204 | -92233.7204 | -92,233.7204 |
9223.372 | 9,223.372 | -9223.372 | -9,223.372 |
922.3372 | 922.3372 | -922.3372 | -922.3372 |
92.2337 | 92.2337 | -92.2337 | -92.2337 |
9.2234 | 9.2234 | -9.2234 | -9.2234 |
0.9223 | 0.9223 | -0.9223 | -0.9223 |
0.0922 | 0.0922 | -0.0922 | -0.0922 |
0.0092 | 0.0092 | -0.0092 | -0.0092 |
0.0009 | 0.0009 | -0.0009 | -0.0009 |
0.0001 | 0.0001 | -0.0001 | -0.0001 |
0 | 0 | 0 | 0 |
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)