+ Reply to Thread
Results 1 to 8 of 8

Format numbers in Indian comma style with negatives in parantheses

  1. #1
    Registered User
    Join Date
    06-06-2009
    Location
    Chennai, India
    MS-Off Ver
    Excel 2000
    Posts
    5

    Format numbers in Indian comma style with negatives in parantheses

    I have found the below mentioned formula to custom format numbers in Indian comma style of crore, lakhs, thousands. The formula does not provide desired results for negatives. Also I am in need of mentioning the negatives in paranthesis i.e negative no. of 1 crore as (1,00,00,000.00). Please give the solution to the above.

    [>9999999]##\,##\,##\,##0.00;[>99999]##\,##\,##0.00;##,##0.00

    Please provide detailed solution since I have only basic knowledge in excel and have not dealt with macros programming.

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

    re: Format numbers in Indian comma style with negatives in parantheses

    Maybe like this:

    Please Login or Register  to view this content.
    Adding a Macro to a Code Module
    1. Copy the code from the post
    2. Press Alt+F11 to open the Visual Basic Editor (VBE)
    3. From the menu bar in the VBE window, do Insert > Module
    4. Paste the code in the window that opens
    5. Close the VBE to return to Excel

    Select the cells you want to apply the formatting to, and then run macro ApplyLakh (do Alt+F8 to bring up the macro dialog, select ApplyLakh, Run)
    Last edited by shg; 06-17-2009 at 11:18 AM. Reason: Corrected as described
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Contributor
    Join Date
    01-18-2005
    Location
    Auckland New Zealand
    MS-Off Ver
    Office Professional 2007
    Posts
    295

    re: Format numbers in Indian comma style with negatives in parantheses

    This page:
    http://exertia.wordpress.com/2006/04...ores-in-excel/
    may be helpful
    Regards
    Mike

  4. #4
    Registered User
    Join Date
    06-06-2009
    Location
    Chennai, India
    MS-Off Ver
    Excel 2000
    Posts
    5

    re: Format numbers in Indian comma style with negatives in parantheses

    Quote Originally Posted by shg View Post
    Maybe like this:
    Many thanks to you. It works. There is no higher amount than crores in Indian numerical system. For example 1 thousand crore is written as
    1,000,00,00,000.00 and 1 lakh crore is written as 1,00,000,00,00,000.00.

    link to relevant wikepedia page
    http://en.wikipedia.org/wiki/Decimal_separator

    The last four lines of digit grouping array may be mentioned like this

    "###\,##\,##\,##0.00_);(###\,##\,##\,##0.00)", _
    "#\,###\,##\,##\,##0.00_);(#\,###\,##\,##\,##0.00)", _
    "##\,###\,##\,##\,##0.00_);(##\,###\,##\,##\,##0.00)", _
    "#\,##\,###\,##\,##\,##0.00_);(#\,##\,###\,##\,##\,##0.00)")

    Is this correct? . If any blank cell is in the range and value is entered after applying macro to it shows the minus sign before it. (for 2 it shows as " - 2")

    Also please edit your post so that other persons who copy can get it right.
    Last edited by shg; 06-07-2009 at 04:37 PM.

  5. #5
    Registered User
    Join Date
    06-06-2009
    Location
    Chennai, India
    MS-Off Ver
    Excel 2000
    Posts
    5

    re: Format numbers in Indian comma style with negatives in parantheses

    Quote Originally Posted by Mikeopolo View Post
    This page:
    http://exertia.wordpress.com/2006/04...ores-in-excel/
    may be helpful
    Regards
    Mike
    Thanks for the Good link. But what stated in it as easy way by changing the regional settings doesn't work.

    regards
    Sripathy

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

    re: Format numbers in Indian comma style with negatives in parantheses

    Prior post edited.

  7. #7
    Registered User
    Join Date
    06-06-2009
    Location
    Chennai, India
    MS-Off Ver
    Excel 2000
    Posts
    5

    Re: Format numbers in Indian comma style with negatives in parantheses

    I could not verify the results of the macro altered due to my exams. Today I have verified it but still need the rectification for

    If any blank cell is in the range and value is entered in that after applying macro to it when it was a blank cell it shows the minus sign before the number entered. (example if number 2 is entered it shows as " - 2"). Please rectify it and post it.

  8. #8
    Registered User
    Join Date
    06-09-2009
    Location
    Dubai, UAE
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: Format numbers in Indian comma style with negatives in parantheses

    Question: would a modified version of the Macro above also provide a viable solution to this problem:

    I have a large table of values denominated in Euros. For some reason, Germans switch commas and decimal points (eg 700.000,00).

    As a result, my American excel program on my American machine does not recognize the numbers properly....

    ....Is there a way to change the symbols without reconfiguring the settings on my entire system each time I want to do this?
    http://www.excelforum.com/excel-gene...minations.html

    If so, I'll work on a way to combine the two to create a VBA script that can be a sort of "Rosetta Stone" for different number systems. My company could sure make use of such a thing.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1