Published:

Edited by: Andrew Leniart

Browse All Articles > Indian number formatting of Currency in Microsoft Access and Excel

The Indian number format for currency has groups of two and three digits. The native Format function of VBA cannot handle this if Windows is set for a Western localisation. Thus, a custom method must be applied. Here it will be shown how to use dynamic formatting to accomplish this.

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

- 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

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

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

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

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 every 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"

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

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.63For 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)

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

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:

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 |

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.

Note: If you need further "Support" about this topic, please consider using the

Get access with a 7-day free trial.

SIGN UP

Not ready to sign up? Try a week for free to see if you belong

## Comments (0)