Indian Number Formatting of Currency in Microsoft Access and Excel

• Download source - 862 B

Introduction

Indian number formatting of currency values for display is so different from what is used anywhere else, that if your Windows is set up for another (typical Western) localisation, you cannot - with the native Format function of VBA - format such values as expected by Indian or Pakistan users.

This is an example:

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

Full documentation can be found on Wikipedia: Indian numbering system.

The expressions listed here solve the issue - for any value of data type Currency - by building the formatted string dynamically, controlled by the value to display, returning either floating or fixed decimals.

No special knowledge is mandatory, though you should be familiar with VBA and the Format function.

Dynamic Formatting

Dynamic Formatting is used when a static format string cannot fit all values expected to be displayed for the user.

This means that different formats are required for one or more ranges of values other than the four the Format function natively can handle, which are these:

• Positive
• Negative
• Zero
• Null

The reason is that the group separator and the decimal separator must be fixed as comma and dot, respectively, thus the usual formatting of the integer and the decimal part cannot be applied. Instead, fixed commas are used for the group separators, and the function Str is used for formatting the decimal value, as Str always returns a dot as the decimal separator.

The final expression to create the formatted value consists of three parts:

• A leading sign (minus) for negative values
• An integer part of any value including zero
• A decimal part for decimal values

As an additional twist, the decimal part can be formatted to have either floating decimals or fixed decimals.

The three parts are concatenated to form the returned string, here for floating decimals:

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)))), "")

For fixed decimals, it is even longer:

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))   

These expression are not practical for extended usage. For such cases, a wrapper function is convenient:

' 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 is used.
'   If Digits is between 0 and 4, fixed decimals is 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

This may still appear a little convoluted, so let's split it apart.

The Sign Part

This is very simple. Its format string ";-" having no code in its positive section (before the semicolon) ignores a positive value and returns an empty string, while the negative section (after the semicolon) only holds a minus sign. The zero section (which would follow after yet a semicolon) is left out; like the empty positive section, this will cause an empty string to be returned for a value of zero.

To summarize: Any negative value will be "formatted" as a minus sign and any other value as an empty string.

Value = -387.89
Format(Value, ";-") -> "-"

The Integer Part

The first task is to obtain the integer and absolute value. Fix, not Int, must be used, as it rounds towards zero.

Value = -387.89
Abs(Fix(Value)) -> 387

Now comes the format section which, for a start, should be this string:

"##\,##\,##\,##\,##\,##\,##0"

However, this will only work as intended for very large values, as smaller values will be returned with a series of leading commas. To overcome this, the format string must dynamically be reduced to fit the value to be formatted, and this must be done in steps of four characters for each two digits:

"##\,##\,##\,##\,##\,##\,##0"
    "##\,##\,##\,##\,##\,##0"
        "##\,##\,##\,##\,##0"
            "##\,##\,##\,##0"
                "##\,##\,##0"
                    "##\,##0"
                        "##0"

A little math is used to cut off the format string. The purpose of CStr is to convert the result to a string for Len to measure. It should not be necessary, you may think, but it is, or the code will not compile:

((Len(CStr(Abs(Fix(Value)))) - 2) \ 2) * 4

The steps are:

• Get the length of the absolute integer value (the count of digits)
• Subtract 2 for the hundreds
• Divide by two
• Round the value down
• Multiply by four

Then Right chops the format string to the found length:

Right("##\,##\,##\,##\,##\,##\,", ((Len(CStr(Abs(Fix(Value)))) - 2) \ 2) * 4)

Finally, the base format string - for small values - is appended to build the full format string for Format:

Right("##\,##\,##\,##\,##\,##\,", ((Len(CStr(Abs(Fix(Value)))) - 2) \ 2) * 4) & "##0"

The Decimal Part (Floating Decimal)

To return only the decimal part, the integer part is subtracted, and Str is used to convert the value to text prefixed with a fixed decimal separator, a dot:

Value = 11.345
Str(Value - Fix(Value)) -> " .345"

However, a negative value would add a leading minus sign:

Value = -11.345
Str(Value - Fix(Value)) -> "-.345"

To prevent this, the absolute value is used:

Value = -11.345
Str(Abs(Value - Fix(Value))) -> " .345"

Finally, LTrim is used to remove the leading space that Str returns for non-negative values:

Value = -11.345
LTrim(Str(Abs(Value - Fix(Value)))) -> ".345"

As for the returned strings in the examples above, note the omitted leading zero, which we don't need. Unfortunately, the decimal part can also be zero - when an integer value is passed - and then a zero is returned by Str:

Str(0) -> " 0"

We don't want that, as the part formatting the integer value also returns a zero, "0", for a value of zero. To omit this, a simple condition is used, which checks for a value of zero and, if so, returns an empty string.

IIf(Value - Fix(Value), LTrim(Str(Abs(Value - Fix(Value)))), "")

The Decimal Part (Fixed Decimal)

Value = 7344.628
RoundedValue = CCur(Format(Value, "0.00"))
RoundedValue -> 7344.63 

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:

Left(LTrim(Str(Abs(Value - Fix(Value)))) & "0000", Sgn(Digits) + Digits)

In the second section (for integer values with no decimals), Mid adjusts the length of the zero value decimal part:

Mid(".0000", 2 - Sgn(Digits), Sgn(Digits) + Digits))

With the sections ready, the final decimal expression can now be assembled:

IIf(Value - Fix(Value), Left(LTrim(Str(Abs(Value - Fix(Value)))) & "0000", 
Sgn(Digits) + Digits), Mid(".0000", 2 - Sgn(Digits), Sgn(Digits) + Digits))

Example Output

This table lists the formatted output of values ranging from the maximum value of data type Currency to the minimum passing zero with floating decimals:

If a fixed format of two decimals were used, the exceeding decimals would be cut off, and the last four rows would all list as 0.00.

If a fixed format of four decimals were used, the value 9223.372 would output as "9,223.3720", and the last row as 0.0000.

Further Information

For full information about the Format function and its format sections, please study the official documentation: VBA Format function.

Conclusion

For optimum service of Indian or Pakistan users, expecting larger numbers to be formatted in the Indian number format, two methods have been presented and explained to be used when Windows is not set up with a localisation that offers this format natively. They can either be applied as "one-liners" for single-case scenarios, or by using the included wrapper function for broader usage. This function can be used like the native function Format of VBA.

History

• 1^st February, 2021: Initial post