Value Of Indian Currency By Serial Number
Vennie Fireman
If you want to display numbers as monetary values, you must format those numbers as currency. To do this, you apply either the Currency or Accounting number format to the cells that you want to format. The number formatting options are available on the Home tab, in the Number group.
In the Negative numbers box, select the display style you want to use for negative numbers. If you don't want the existing options for displaying negative numbers, you can create your own number format. For more information about creating custom formats, see Create or delete a custom number format.
When you apply the Currency format to a number, the currency symbol appears right next to the first digit in the cell. You can specify the number of decimal places that you want to use, whether you want to use a thousands separator, and how you want to display negative numbers.
Like the Currency format, the Accounting format is used for monetary values. But, this format aligns the currency symbols and decimal points of numbers in a column. In addition, the Accounting format displays zeros as dashes and negative numbers in parentheses. Like the Currency format, you can specify how many decimal places you want and whether to use a thousands separator. You can't change the default display of negative numbers unless you create a custom number format.
If you often use currency formatting in your workbooks, you can save time by creating a workbook that includes specific currency formatting settings, and then saving that workbook as a template. You can then use this template to create other workbooks.
Tip When multiple worksheets are selected, [Group] appears in the title bar at the top of the worksheet. To cancel a selection of multiple worksheets in a workbook, click any unselected worksheet. If no unselected sheet is visible, right-click the tab of a selected sheet, and then click Ungroup Sheets.
I wrote the following number format utility recently which can be used in OP's case as well as others. It should hopefully be fairly straightforward to amend it to cover other cases (I expect most would like to change my default values for example).
You can also add this in each .js or .tsx file that you need or also to not add the entire function, you can create a file in the root path that is of type context with 'createContext()' you can guide yourself more here 'Guide to using 'createContext()'', with that you will only have to get the function from that context file and it is less code to render in your .js or .tsx files. I hope I've helped :)
@Cyril.. not sure why, but I think we are used to counting in hundreds and we have established notations for hundred thousand - lakh and hundred lakhs - crore. May be they would have just adopted the convention of separating hundreds when they started writing numbers in script (and eventually on to computers).
Just want to point out that suggested Custom Format will only handle non-floating point numbers and apparently will only correctly format a maximum of nine digits. If you want to be able to handle up to 15 significant digits, including a forced display of two decimal places, then here is some worksheet event code that will automatically format the cell "on the fly" (just set the range being assigned to the R variable to the range of cells you want to have an Indian Number Format)...
For those who have not worked with event code before, you would install this code by right-clicking the name tab at the bottom of the worksheet you want to have this functionality, click "View Code" on the popup menu that appears and then copy/paste the above code into the code window that appeared. That's it! Now go back to the worksheet and type in a number with up to 15 significant digits into Column C (if you left my code alone) or into whatever range you changed my example "C:C" cell range to in the assignment to variable R.
For more information on the Indian numbering system you can see _Asian_numbering_system but to be frank we never heard of any number above 10 power 7... surprised to see that other terms are infact captured here!
Anyway, I looked up the balance sheet for the State Bank of India. -bank-of-india/14030001/balance-sheet... I got so wound up trying to reparse the numbers (from hundreds, thousands, lakhas and crores TO hundreds, thousands, millions, billions) that I immediately lost track of the importance of the information content... and then there was the (Rs crore) notation on the bank information which told me that the parsing process was going to be even more complicated... and that does not even factor in currency exchange rates to get to a point of relative reference (in my case back to dollars).
Chandoo tells us that: Large amounts of Rupees are expressed in lakh rupees or crore rupees. A Lakh Rupee is one hundred thousand rupees and a crore rupee is ten million rupees. And a little more digging gives these additional two digit group names: arab (one billion rupees), kharab (one hundred billion), neel (ten trillion) and padma (one quadrillion).
So one question:
Is this two digit comma notation used only for India Rupee curency value or is it used also used in India for volume measures (number of shares, tonnes of rice or bales of tea, etc)?
As suggested, using the "Kannada (India)" choice at the operating program level (Windows Vista) will immediatly switch everything not formatted in something specific to the two digit comma offset notation.
Two more questions:
>When I assign the Currency format with the symbol as "Rs. (English) India" to a cell and then look at it under custom formatting I get "[$Rs.-4009] #,##0" what does the "-4009" mean? This number changes for different currency symbols... but I cannot figure out the logic.
@bill.... I think the problem you had with my code is due to the comment processor Chandoo uses... look at ALL the quote marks in all the lines of code... they are NOT the quote marks you need to use in the VB code editor. If you delete each one and replace it with one typed in from your keyboard, my code should work then.
2) The Rupee symbol has been submitted for unicode approval. Once approved, I assume various font makers (and Microsoft, Apple etc.) would include the unicode and corresponding symbol in their distributions. Only then you can get it in to Excel , word etc with ease. Until that time you have to use a font like as Ninad points out above.
Rick, your suggestion worked. I retyped (in the VBA editor) all of the code that was not alphabetic giving particular attention to the quote marks and the minus character... and it worked. Very interesting VBA, this opens up all sorts of possibilities. Thank you.
Quick note: I have noticed that there is even a small sweet spot for formulas (placed within the R range) that yield numbers between one hundred thousand (a lakah) and nine hundred ninty nine thousand nine hundred and ninety nine (one short of a crore). Why does this this VBA parsing work at all on a formula?
@rick - your revised code works perfectly. Thank you.
By the way, I found that if you add "[$Rs. ] & " after the equal sign and before the "Trim" function in the NumberFormat line you get the current rupee sign before the correctly formatted numeric string.
In experimenting with this I have found that the only issues are with negative numbers. The negative sign pops out to the left of the Rs symbol and a zero value does not format properly (with the Rs addition). I am thinking that by adding conditional statements (LT 0, = 0, and GT 0) are set up at the IF loop where the number is being evaluated than this might lead to handling these outlier cases. I have not yet had a chance to try it.
bill
Sir, Now recently our Indian Government declared a notation for our currency. How can we include in our doc, since the notation is not available on the web, I think. Would you please guide me for this. With regards
Thanks for the info on formatting spreadsheets for INR. This approach works well for me except that I need it to display negative numbers in RED, and I can't figure out how to add that to your custom cell format:
[...] "Lacs "##0.00;[Red]-"Lacs "##0.00;0 For more on formatting Indian currency see this link How to show Indian Currency Format in Excel? How to show Indian Number Formats in Excel? Chandoo.o... [...]
Above custom number formating works fine with positive numbers. However, if I want to display negative numbers in [Red] and I do not want to change windows level setting. How it is to be done.
Two heads up:
1. No VBA code please
2. Only Custome Number formats to be used.
I actually used this to change from lacs format to thousand as this is pretty much a standard. Unfortunately my laptop came with a setting which only allowed lacs format. Your tips defenitely helped. Thanks a ton.
I am grown up man, I mean, have been using Lotus in MS DOS times (early 90s). Is not Microsoft Excel designed by idiots whose minds did not cross to add simple few lines of codes to allow for Indian digit grouping in Excel (not through universal Control Panel) as a cell format option, in all these decades ???
For this specific topic, I see there is a solution for +ve numbers. There is a separate solution for -ve numbers. Do you have a single solution to handle both -ve and +ve numbers? I want to do accounting in my worksheets.
Create the appropriate string by appending string "rupees" and "paise" at required places in the text field where you would be displaying the value. this is what I could think of at this point of time. Try it out. This should work.
This would give you the value of number (before the decimal) in words. Of course, you need not assign this value to a field as I did. this was just an illustration and you can use a variable in script to store values.Similarly, you can do the same for number after the decimal.
I have a numeric field NumericField1, TextField1 displays its value in dollars/cents and TextField2 displays rupees/paise. Try this script written on exit event on numeric field once a value is entered in numeric field. You can customize the script based on your use case.
c80f0f1006