fbpx
Search
Close this search box.

How to show Indian Currency Format in Excel?

Share

Facebook
Twitter
LinkedIn

This is a guest post by our excel ninja – Hui.

Indian Currency Formatting, Number Formatting in ExcelToday we will learn how to format numbers and amounts in Indian currency format. Indian numbers are grouped differently than standard English numbers.

English Grouping              123,456,789.01

Indian Grouping               12,34,56,789.01

Quick and easy fix to show numbers in Indian format:

A custom number format of:

[>=10000000]##\,##\,##\,##0;[>=100000] ##\,##\,##0;##,##0

or

[>=10000000]"RS "##\,##\,##\,##0;[>=100000]"RS " ##\,##\,##0;"RS "##,##0

Will solve the problem.

A permanent solution to Indian Number Formats:

However there is better and more permanent solution using the Systems Regional Settings

  1. Goto the Control panel and select the “Region and Language” button
  2. Select “Additional Settings”
  3. Set digit groupings to the Indian grouping like this:
    Indian Currency Formatting, Number Formatting - Settings in Control Panel
  4. Do the same on the Currency Tab and Apply

Now in Excel the Default  “,” and “$” Style will show the way you wanted.

Thanks Hui:

Thank you so much for sharing this hack with all of our readers. 🙂

More Quick tips & Number Format tricks.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

94 Responses to “How to show Indian Currency Format in Excel?”

  1. Cyril Z. says:

    Wow ! I wasn't aware of that kind of grouping. I'm so stuck in "occidental"-thousand grouping way of thinking...

    Can you explain the historical/cultural reason for that grouping ?

    Cyril.

  2. jpc says:

    I don't have additional setting option in region and language button. Is this facility available only in vista and window 7?

  3. Dominik Petri says:

    Hi Chandoo,

    didn't know about different grouping in India! Very interesting...

    I don't have time now to work out the "indian style", but if you are curious about what can be done in regards of international number formats, check out these webpages:

    http://office.microsoft.com/en-us/excel-help/creating-international-number-formats-HA001034635.aspx

    http://www.science.co.il/language/Locale-Codes.asp

    Regards,
    Dominik.

  4. Leonel Q. says:

    Interesting, but could you explain what the backslashes are for? I've never used a backslash in a custom format.
    Regards.

  5. Chandoo says:

    @Leonel .. the backslash forces excel to treat , as a comma instead of thousand's separator.

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

    @Dominik.. good links, thanks for sharing.

    @JPC what version of windows are you on?

  6. Rick Rothstein (MVP - Excel) says:

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

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim R As Range
    Set R = Range("C:C")
    If Not Intersect(Target, R) Is Nothing Then
    With Target
    .NumberFormat = Trim(Replace(Format(String(Len(Int(.Value)) - 1, "#"), _
    " @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), " \,", "")) & ".00"
    End With
    End If
    End Sub

    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.

  7. Ninad Pradhan says:

    You can also download the new font from http://blog.foradian.com/ (it's not official - I think) and is also mapped to the 'tilde' key (above Tab).

    Ninad.

  8. Naga says:

    For more information on the Indian numbering system you can see http://en.wikipedia.org/wiki/South_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!

  9. bill says:

    hank you very much for this thought provoking article. A few rather random thoughts:

    The notation needed for India currency was also news to me... very interesting a learning experience. I can't believe how provincial I feel.

    Anyway, I looked up the balance sheet for the State Bank of India. http://money.rediff.com/companies/state-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 to the formatting formula there seems to be no way to add another filter term to this formatting line "[>=10000000]"Rs. "##\,##\,##\,##0;[>=100000]"Rs. " ##\,##\,##0;"Rs. "##,##0" and if you just add more "##\," to the format and the number is less than 10000000 then you get a lot of commas at the left side of the number.

    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.

    I could not get the VBA programming (suggested by Rick) to work. When I implement this it gets hung up on the "Set R" code line and the ".Number" code row turns a nasty red color.

    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.

    >What needs to happen to get the new India Rupee symbol available to Excel users worldwide?

    -bill

  10. Rick Rothstein (MVP - Excel) says:

    @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.

  11. Chandoo says:

    @Bill.. very interesting points you make. You are right in guessing that Indians often use the hundred / lakh /crore notation for measuring other numeric things, although we do follow metric system.

    Also, your questions:

    1) I dont know what the -4009 means either. May be someone can tell us what is the mystery behind this.

    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 http://blog.foradian.com/ as Ninad points out above.

  12. bill says:

    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?

    Bill

  13. Rick Rothstein (MVP - Excel) says:

    Yes, it looks like that line has to be retyped in its entirety... I just copied it into my VB editor and the quote marks seemed not to be the only characters that are causing problems (I suspect some of those "space characters" are not true spaces... retying the entire line cleared all syntax errors. Anyway, to your question about the code working for values displayed by formulas... actually, it doesn't work for formula values. What I think you are seeing is a left over Custom Format (what my code does) in a cell you changed from a constant to a formula. I think if you change the formula to display number differing greatly in magnitude, one or both will display incorrectly. HOWEVER, your statement caused me to go back and re-work the code so it responds correctly whether the cell contains a constant or a formula. As with my previous posted code, if you get a syntax error from the copy/paste, just retype the offending line of code manually and it should clear the syntax error. Here is the now more robust code...
    -
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim R As Range, Cell As Range
    Set R = Range("C:C")
    If Not Intersect(Target, R) Is Nothing Then
    With Target
    If WorksheetFunction.IsNumber(.Value) Then
    .NumberFormat = Trim(Replace(Format(String(Len(Int(.Value)) - _
    1, "#"), " @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), _
    " \,", "")) & ".00"
    Else
    .NumberFormat = "General"
    End If
    End With
    ElseIf Not Intersect(Target.Dependents, R) Is Nothing Then
    For Each Cell In Target.Dependents
    If Not Intersect(Cell, R) Is Nothing Then
    With Cell
    If .Value "" Then
    If WorksheetFunction.IsNumber(.Value) Then
    .NumberFormat = Trim(Replace(Format(String(Len(Int(.Value)) - _
    1, "#"), " @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), _
    " \,", "")) & ".00"
    Else
    .NumberFormat = "General"
    End If
    End If
    End With
    End If
    Next
    End If
    End Sub

  14. Rick Rothstein (MVP - Excel) says:

    A follow up to my latest posted code... besides needing to retype some of those lines of code because of non-standard character conversions by Chandoo's comment processor, I see one of the lines had its "not equal to" symbols... a less than sign followed by a greater than sign... removed by the comment processor as well. So, this line of code...
    -
    If .Value “” Then
    -
    should read this way instead....
    -
    If .Value .NE. "" Then
    -
    where you would replace the .NE. with a less than sign followed by a greater than sign.

  15. Amit Joshi says:

    Thank you for the perfect solutions for excel. I resolved my problem through this ..

  16. Oh! thank God for this!

    I am going to try it right away!

  17. bill says:

    @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

  18. Rick Rothstein (MVP - Excel) says:

    @bill,

    It looks like I omit the error checking routine to protect against no dependents for the target cell in my last posted event code. To correct for this, add this line of code...

    On Error GoTo NoDependents

    immediately after the first Set statement at the top of the code and add this statement...

    NoDependents:

    as the last line of code (immediately above the End Sub statement).

  19. R.Ravi says:

    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

  20. Rick Rothstein (MVP - Excel) says:

    @R.Ravi,

    Can you make use of the information at this link?

    http://en.wikipedia.org/wiki/Indian_numbering_system

  21. Rahul. says:

    Hello Chandu Bhai.

    I have tried the regional option's and it does not group.

    As soon as I apply the changes, all other changes, Rs. and decimal places, happen instantly. Not the grouping. Just not happening! 🙁

    Help!

    Ra.

  22. Native says:

    Thanks a ton for Regional setting idea! For a year I haven't been able to figure that out and had waster lot of my time. Thanks for the tip!

  23. shamisha says:

    Thanks Ninja-HUI,
    Great! Nice formatting tips. It helps.

  24. [...] To use Indian formatting for numbers (i.e. Lakh, Crore instead of Millions) see this article: “Indian Currency format in Excel” [...]

  25. Rakesh Kumar Mishra says:

    How can we chang the US doller into indian currency in balance sheet (Excle file).

  26. Hui... says:

    @Rakesh
    You can do it a number of ways
    1. Add extra Rows which are in Indian currency
    2. Add a multiplier to all the cells
    =Current Formula * ExchRate
    where ExchRate is a named formula for the exchange rate
    3. Copy the exchange rate from a cell and Paste Special; Multiply, over the whole area

    • Martin says:

      Hi

      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:

      [>=10000000]"RS "##\,##\,##\,##0;[>=100000]"RS " ##\,##\,##0;"RS "##,##0

      According to the tutorials, everything before the first ';' is for positive numbers, and before the second ';' is for negative numbers, but it looks like you're using both areas for positive numbers.

      Can you help me please?

      Thanks

      Martin

      • Martin says:

        @Hui

        Thanks for the custom formats for INR. Much appreciated.

        One issue I've run into with what you've proposed is that the formatting doesn't always work for negative numbers.

        To see what I mean, please put these two numbers into adjacent cells and then apply the custom format for INR on it:
        -1897400
        1897400

        When I do this I get the correct formatting for the positive number, but not the negative number. I think that this may be because (according to the tutorials) everything before the first ‘;’ is for positive numbers, and before the second ‘;’ is for negative numbers, but it looks like you’re using both areas for positive numbers.

        Thanks

        Martin

  27. AMIT KUMAR says:

    Hi, Thanks Hui for this nice solution... u made my work easier... thanks a lot

  28. Chood says:

    Thanks Hui.

    -Chood

  29. Raghu says:

    Thanks friend,
    It saved lot of time for me.
    Regards,

  30. murali says:

    Thanks dear friend

  31. Ken says:

    You deserve a big thanks.... Your a genius

  32. Aviator says:

    This is a simple and great solution to a problem that had been bothering me for long.  Thank you very much, Hui. You really are a genius!

  33. [...] "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... [...]

  34. shyamala says:

    nice .................!
    helped me a lot...............

  35. SAN says:

    My excel appear 10,00,000.00 like that, anyone please tell me how to change to 1,000,000.00. I was try with several method but not possible.

    • Hui... says:

      @San
      Most people in India want it to display like yours and can't
      It can be one of two things
      1. The local currency or number format in Windows is set like that
      Its the opposite of the instructions above labelled"
      A permanent solution to Indian Number Formats:"
      2. Change the number format of the cells
      Select some cells
      Ctrl 1
      Custom
      In the Type: Box type: 0,000.00
      apply

  36. Sachin says:

    Thanks Chandoo,
    It was very very useful.
    Regards,
    Sachin

  37. naveen sharma says:

    Thanks Chandoo,
    I.
    Regards
    naveen

  38. mangi says:

    Thanks lot for the solution.

  39. Jyoti says:

    Hi Hui,

    Thank you so much you solved my problem

  40. Abhay G says:

    Hi Chandoo,

    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.

    • Abhay G says:

      I thought of workaround by using conditional formatting. But, I want to save this Indian format in Cell Styles so that I can easily apply whenever I need it.

  41. ragav says:

    excel ninja-hi this works well for positive numbers. ofr negative numbers it is not.
    any ideas?

  42. Vinod James says:

    Thanks Chandu This is a great help...!!!!

    I have try to seek this option from last 5 years

    but the option you told here fully valid and permanent.

    Thanks again

  43. Kailas Gaikwad says:

    Thanks a lot, i am unaware of it, it helps my account department.

  44. Ajeet says:

    Thanks a Ton Chandoo for the great tip !
    The Regional setting idea never occurred to me. Have been trying to tinker with the Custom Format in Excel and wasting time !

  45. Nikunj says:

    I used a code similar to one published by someone in comments above

    .NumberFormat = Trim(Replace(Format(String(Len(Int(.Value)) – 1, “#”), _
    ” @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0?), ” \,”, “”)) & “.00?

    Is there a way to format negative numbers with brackets

    i.e. -5,00,000 should be displayed as (5,00,000)

    0 should be displayed as -

    Also, using this code 3-digit numbers such as 5,000 show up as ,,5,000 and numbers 3-digit numbers such as 56 show up as ,,,56

    Is there a way tp sort this?

    The full code i am using is

    Sub IndianNumberFormat()
    For Each C In Selection
    C.NumberFormat = Trim(Replace(Format(String(Len(Int(C.Value)) - 1, "#"), _
    " @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), " \,", "")) & " "
    Next
    End Sub

    P.S: I'm an excel noob and have picked up this code from a forum i came across.

  46. Dheeraj says:

    Above format style doesn't work with negative values.
    e.g. - When value is positive it shows 5,00,000 but when value goes to negative it shows -500,000. Is there any way to tweak that ??

    • Pramathesh Borkotoky says:

      You can try this [>=10000000]_ [$?-4009] * ##\,##\,##\,##0.00;[>=100000]_ [$?-4009] * ##\,##\,##0.00;_ [$?-4009] * ##,##0.00

  47. Rajesh says:

    Thankyou very much Sir,
    its very useful.

  48. Wang says:

    Thank you for the great resource. How would you do it for Chinese yuan?
    1 Wan = 10000 = Ten thousand = 10,000
    1,000,000,000 = 10 Yi = 10,0000,0000

  49. Santhi says:

    Hi,
    Thank you so much.
    I have try to seek this option, but the option you told here fully valid and permanent.
    again Thank You so much

  50. Iedc says:

    Cool ! Thanks

  51. Ketan says:

    Thanks man
    it is really helpful

  52. Bhuvaneshwer says:

    Hi Chandoo,

    I have done all the setting in system (using Win8 Machine) to make it Indian format but in excel it is still showing upto lacs correctly but in crores it is again not taking (sample ? 134,55,000).

    I used custom setting for each cell that worked.

    Is there any permanent Solution because every-time i have to format cell.

    Thank you in advance.

  53. Rijo says:

    Thanks a lot for your Instruction.

  54. ViN says:

    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.

    Regards,
    ViN

  55. Raymond says:

    How do u change the $ or the Rs. Symbol to the new indian rupee symbol in the Format Cells columns in excel.
    Please help been trying for quite sometimes now.

    Regards
    Raymond.

  56. Deepak says:

    Thanks, it worked for me!

  57. K BALAJI says:

    Really Its very wonderful for me

  58. Manas says:

    A minor change to what has been suggested:

    [>=10000000][$?-4009] * ##\,##\,##\,##0;[>=100000][$?-4009] * ##\,##\,##0;[$?-4009] * ##,##0

    This will introduce the INR symbol and format the cell as per accounting view (rather than a currency view). That is, left aligned rupee symbol and right aligned numeric value.

    Hope this helps!
    Regards,
    MT

    • Manas says:

      Oops, guess the font of this discussion page does not have the INR rupee symbol !!

      Hence, whosoever wishes to use the above suggested custom format, please replace "?" in the custom format with the rupee symbol.

  59. Krishnam says:

    Thank You, Thank You, Thank You for the helpful post.

  60. kmr says:

    tnx a ton

  61. Suresh Raju says:

    Thanks a lot. I just tweaked a little with Indian currency symbol and with decimal(paise) values.

    [>=10000000]"? "##\,##\,##\,##0.00;[>=100000]"? "##\,##\,##0.00;"? "##,##0.00

  62. sathish says:

    i changed in region settings... but its not working in the excel sheet... can u help me to solve this,,????

  63. Jitender Sharma says:

    Dear Chandoo,
    Thank you so much for the solution 🙂
    Keep posting stuff like this....

  64. Hello Purna,

    We have made an easy method to convert amount into Indian currency in words.

    You can check it out here https://www.teachoo.com/860/279/Convert-amount-in-words-(Indian-currency:-Lakhs,-Crores)/category/Macros/

    Can you please link to us as well?

    Regards
    Ritika

  65. naved says:

    thanks you ....

  66. Yogesh Bindal says:

    Hi Chandoo,

    i want 10 K instead of 10000, 10 Lac instead of 1000000, 11 Cr instead of 110000000

    Can you help me for this....

  67. Thomas says:

    I tried the custom format but it doesn't show the the digits (Paise).

  68. Son Day says:

    Still no format to get negative numbers in Lakhs & Crores.........?????

    Anyone????

  69. Arun says:

    It worked & thanks a lot

  70. Vinay Bhatt says:

    Custom format solution for both positive & negative numbers:

    For positive numbers, use the custom (up to 99.999999 crore)
    [>=10000000][$?]##\,##\,##\,##0.00; [>=100000][$?]##\,##\,##0.00; [$?]##\,##0.00

    '?1,55,00,880.05
    ?1,55,880.05
    ?55,880.05
    ?,58.00

    For negative numbers, use the custom format: (up to -99.999999 crore)
    [<=-10000000][$?]-##\,##\,##\,##0.00; [<=-100000][$?]-##\,##\,##0.00; [$?]##\,##0.00

    '?-1,55,00,880.05
    ?-1,55,880.05
    - ?55,880.05
    - ?,580.00

    I am not able to get rid of frontal , in small numbers.
    Also, can I have one formula that will work with both positive & negative numbers?

    -Vinay Bhatt, Austin, Texas

    • Ahmed says:

      Thanks for asking.

      I am also looking for universal formula for positive and negative numbers as profit or loss will keep changing for various scenario.

      Control Panel option is not smart one, for, many a times you want to show the USD amounts in one column in US Format and equivalent INR amounts in Indian digit grouping format in adjacent column.

    • Ahmed says:

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

  71. Vinay Bhatt says:

    ? in my comment above should be interpreted as RS symbol.

  72. Arun says:

    Thanks a Lot...

  73. OMEE says:

    thanx a lot bro my boss is an asshole. he make me angry for this tiny problem. now i fix it and chill thanx again

  74. Rajesh says:

    Thank you

  75. JAY SORATHIA says:

    Thank you, Chandoo for your hard work and contribution.

  76. Jerome says:

    The solution given for indian system applies only for positive numbers any solutions that includes zero and negative numbers

  77. THANESHWAR SINHA says:

    Formet Cell ME JAKAR Accounting+English (India) NHI AA RHA HAI KAISE LAYE.
    OR COMMA THUNDRED,THOUSAND,LACS,CORRE KAISE SET KARE

  78. Chintan says:

    Superb! thank you so much! Works flawlessly!

  79. Selva says:

    Thank you! works perfectly!

    If anyone would like to use it with the Currency Symbol, Please use the below one:

    [>=10000000]"? "##\,##\,##\,##0;[>=100000]"? " ##\,##\,##0;"? "##,##0

  80. Nilesh Parkhe says:

    Is it possible to recreate the format in TEXT command?

  81. Ahmed says:

    Thanks, brilliant.
    Searched everywhere, but found on your website.

    However, it does not work for negative numbers when I tried by reversing the More Than sign with Less Than.

    Please help.

    M. Ahmed

  82. RK says:

    I want to format rupees in Lakhs with Comma's, I tried the below noted formatting, However the results were not coming with comma's. Can you help in this regard

    _(#0\.00,;_(* (#0\.00,);_(* "-"??_);_(@_)

  83. Kumar says:

    Hi,

    The below formula is applied to two cell in excel A1 & B1.
    [>=10000000]" "##\,##\,##\,##0;[>=100000]" " ##\,##\,##0;" "##,##0
    The resulting number format will be lie this X,XX,XX,XXX

    Below Cases
    --------------
    A1=10,00,000 & B1=8,00,000

    Case 1 : When I subtract B1 from A1 (ie., 10,00,000 - 8,00,000), the result obtained is 2,00,000 (positive number). it is satisfying the above number format
    Case 2 : When I subtract A1 from B1 (ie., 8,00,000 - 10,00,000), the result obtained is -200,000 (negative number), here it is failing the above number format.

    Pl. help in resolving to get the result to be shown as -2,00,000

    Thanks
    KSK

  84. Animesh says:

    First of all, thanks for the great website. I keep following it for many years.

    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.

Leave a Reply