Indian Comma style

Shital Shah

unread,

Apr 12, 2004, 7:19:43 AM4/12/04

to

I want to use Indian Comma style upto 100 crores. with -ve
value have to come in ()Bracket.

like.
100
1,000
10,000
1,00,000
10,00,000
1,00,00,000
10,00,00,000
1,00,00,00,000
with -ve
(100)
(1,000)
(10,000)
(1,00,000)
(10,00,000)
(1,00,00,000)
(10,00,00,000)
(1,00,00,00,000)
is any help

Bernard V Liengme

unread,

Apr 12, 2004, 10:03:16 AM4/12/04

to

Hello Shital,
This is taken from an answer given by David McRitchie in a post earlier this
year:
<Quote>
The following addresses the placement of the comma not numbers
into words:

If you have Excel 2002 or later look up bhattext in help.
Otherwise the following format might help you

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

(This format separates groups for India/Thailand, format valid for
positive numbers up to 99,99,99,999.99 or 999,999,999.99)
in Excel XP see function BhatText as in Thai Bhat currency.
=BAHTTEXT(A37) [Rupees, Rs., Paise, lakhs, crores] ==
an alternative may be to use Control Panel, Regional Settings, Numbers,
digit grouping.

The above can be found on my formula.htm page, the hidden
links in the above text from that page include

http://office.microsoft.com/assistance/offhelp/offxp/excel/xlmain10/html/Bahttext.htm
http://google.com/groups?selm=OwOZ4VaBBHA.1812%40tkmsftngp02

http://google.com/groups?threadm=3B82C702.75F3C989%40NoMoreSpammodelfitness.com

<unquote>
I found this using a Google newsgroup search
Bernard

"Shital Shah" <shahs...@rediffmail.com> wrote in message
news:1b85101c42080$0e200a20$a101...@phx.gbl...

David McRitchie

unread,

Apr 12, 2004, 10:08:34 AM4/12/04

to

To do it right you will probably have to use Excel XP,
but then with the International version of Excel, it could
be in your Regional Settings.

This is what you can do in earlier versions of Excel
for positive numbers, and I see you definitely indicated
you want both postive and negatvie.

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

(This format separates groups for India/Thailand, format valid for positive numbers up to 99,99,99,999.99 or 999,999,999.99) in
Excel XP see function BhatText as in Thai Bhat currency. =BAHTTEXT(A37) [Rupees, Rs., Paise, lakhs, crores] == an alternative may
be to use Control Panel, Regional Settings, Numbers, digit grouping

http://office.microsoft.com/assistance/offhelp/offxp/excel/xlmain10/html/Bahttext.htm
http://groups.google.com/groups?selm=OwOZ4VaBBHA.1812%40tkmsftngp02
http://groups.google.com/groups?threadm=3B82C702.75F3C989%40NoMoreSpammodelfitness.com

---
HTH, (above from my formula.htm web page)
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Shital Shah" <shahs...@rediffmail.com> wrote in message news:1b85101c42080$0e200a20$a101...@phx.gbl...

Shital

unread,

Apr 13, 2004, 7:37:56 AM4/13/04

to

Thanks for ur replay

Can i get India Format style in -ve. that is in (Bracket)

Shital

>> value have to come in ().

>>
>> like.
>> 100
>> 1,000
>> 10,000
>> 1,00,000
>> 10,00,000
>> 1,00,00,000
>> 10,00,00,000
>> 1,00,00,00,000
>> with -ve
>> (100)
>> (1,000)
>> (10,000)
>> (1,00,000)
>> (10,00,000)
>> (1,00,00,000)
>> (10,00,00,000)
>> (1,00,00,00,000)
>> is any help
>>
>
>

>.
>

Norman Harker

unread,

Apr 13, 2004, 10:43:00 PM4/13/04

to

Hi Shital!

A bit late but the question is unanswered and is probably in common
demand in India and elsewhere.

There's a problem with "manual" formatting to handle negatives because
the space in the format string usually used for negatives is taken up
with handling positives.

Here's a VBA solution that can be applied to existing number entries:

Sub IndianFormat()
Dim Cell As Range
For Each Cell In Selection
With Cell
Select Case Len(Abs(Int(.Value)))
Case Is <= 3
.NumberFormat = "###;(###)"
Case Is <= 5
.NumberFormat = "##,###;(##,###)"
Case Is <= 7
.NumberFormat = "#\,##\,###;(#\,##\,###)"
Case Is <= 9
.NumberFormat = "#\,##\,##\,###;(#\,##\,##\,###)"
Case Is <= 11
.NumberFormat = "#\,##\,##\,##\,###;(#\,##\,##\,##\,###)"
End Select
End With
Next Cell
End Sub

I'm sure it can be improved upon and there's no reason why it can't be
used in a Worksheet_Change event:

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Range("A1:A20")) Is Nothing Then
Exit Sub
End If
With Target
Select Case Len(Abs(Int(.Value)))
Case Is <= 3
.NumberFormat = "###;(###)"
Case Is <= 5
.NumberFormat = "##,###;(##,###)"
Case Is <= 7
.NumberFormat = "#\,##\,###;(#\,##\,###)"
Case Is <= 9
.NumberFormat = "#\,##\,##\,###;(#\,##\,##\,###)"
Case Is <= 11
.NumberFormat = "#\,##\,##\,##\,###;(#\,##\,##\,##\,###)"
End Select
End With
End Sub

Adapt the range in the first line of the code to whatever range you
want to apply it to.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njha...@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Shital" <shahs...@rediffmail.com> wrote in message
news:1bc3701c4214b$c3f71430$a501...@phx.gbl...

David McRitchie

unread,

Apr 14, 2004, 12:20:07 AM4/14/04

to

Thanks for the update Norman to handle negative and positive,
I've added a reference to this thread on my formula.htm webpage.

What does -ve stand for (other than negative numbers).

I notice that no decimal point was used, I've been supplying
a decimal point all along and don't really know but that the
values may not use fractions.

There is a drawback to the event macro in that it is a change
event macro, so if a calculation (formula) changes the value
then the format will not be changed -- also you do have to
specify the column or the area that the event macro is to
encompass, but since it only applies to the sheet that part
should not be a problem -- just has to be customized.

Since this is not the programming newsgroup,
instructions to install use a macro in
http://www.mvps.org/dmcritchie/excel/getstarted.htm

instructions to install and use an Event macro in
(right click the sheet tab and insert the macro), but
there is more information about Event macros here
http://www.mvps.org/dmcritchie/excel/events.htm
An Event macro I think would be preferred to running
the subroutine also supplied. You can test the range
with something like
If target.column <> 3 then exit 'not column C

Of course in Excel XP you can use BHATTEXT, but for
compatibility and for this user that is not an option.
---
HTH,

David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Norman Harker" <njha...@optusnet.com.au> wrote ..

Norman Harker

unread,

Apr 14, 2004, 11:41:48 AM4/14/04

to

Hi David!

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njha...@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

"David McRitchie" <dmcri...@msn.com> wrote in message
news:OyvQhgdI...@TK2MSFTNGP09.phx.gbl...

> Thanks for the update Norman to handle negative and positive,
> I've added a reference to this thread on my formula.htm webpage.

That link isn't coming up yet.

It could be quite useful for the Indian users but I wonder if they
have a format in the Indian version of Excel / Windows

>
> What does -ve stand for (other than negative numbers).

I've only ever seen it as a shorthand for negative.

>
> I notice that no decimal point was used, I've been supplying
> a decimal point all along and don't really know but that the
> values may not use fractions.

You can insert decimal places in the code. But you get nasty problems
if you use the code and then try to increase the decimal places . It
really needs the addition of decimal places after the basic strings to
give you for the basic subroutine:

Sub IndianFormat()
Dim Cell As Range
For Each Cell In Selection
With Cell
Select Case Len(Abs(Int(.Value)))
Case Is <= 3

.NumberFormat = "###.00;(###.00)"
Case Is <= 5
.NumberFormat = "##,###.00;(##,###.00)"
Case Is <= 7
.NumberFormat = "#\,##\,###.00;(#\,##\,###.00)"
Case Is <= 9
.NumberFormat = "#\,##\,##\,###.00;(#\,##\,##\,###.00)"
Case Is <= 11
.NumberFormat = "#\,##\,##\,##\,###.00;(#\,##\,##\,##\,###.00)"

End Select
End With
Next Cell
End Sub

>

> There is a drawback to the event macro in that it is a change
> event macro, so if a calculation (formula) changes the value
> then the format will not be changed -- also you do have to
> specify the column or the area that the event macro is to
> encompass, but since it only applies to the sheet that part
> should not be a problem -- just has to be customized.

Agreed on the drawback of the event macro. An interesting
customization problem with a need to watch for changes in the
variables that affect the values that are subject to this format. I
might play with this although I think that for a sustantial
application this starts getting difficult and power hungry.

>
> Since this is not the programming newsgroup,
> instructions to install use a macro in
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> instructions to install and use an Event macro in
> (right click the sheet tab and insert the macro), but
> there is more information about Event macros here
> http://www.mvps.org/dmcritchie/excel/events.htm
> An Event macro I think would be preferred to running
> the subroutine also supplied. You can test the range
> with something like
> If target.column <> 3 then exit 'not column C
>
>
> Of course in Excel XP you can use BHATTEXT, but for
> compatibility and for this user that is not an option.

I don't think that BAHTTEXT is much help for this problem even for the
Thais. It only converts the number to Thai words (something that is
needed quite frequently by users everywhere).

Shital

unread,

Apr 18, 2004, 6:41:19 AM4/18/04

to

Dear sir,
Mr. Norman Harker

Thank you very much.
I'ts working fine.

thanks again

Shital shah

>.
>

Norman Harker

unread,

Apr 18, 2004, 10:38:42 AM4/18/04

to

Hi Shital!

Thanks for thanks and confirmation is always good for Google
searchers.

You will need to modify for inclusion of decimals or if you want
larger numbers.

Out of curiosity. Do you know if the Indian version of Excel provides
a format option for the one commonly used in India?

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njha...@optusnet.com.au

It is imperative that the patches provided by Microsoft in its April
Security Release be applied to Systems as soon as possible. It is
believed that the likelihood of a worm being released SOON that
exploits one of the vulnerabilities addressed by these patches is VERY
HIGH.
See:
http://www.microsoft.com/security/protect/

Shital

unread,

Apr 19, 2004, 7:00:13 AM4/19/04

to

thanks for reply .

No, Indian version of Excel doesn't provides a format
option for the one commonly used in India.

>.
>

Norman Harker

unread,

Apr 19, 2004, 7:05:00 AM4/19/04

to

Hi Shital!

Thanks for that.

I'll put it up to Microsoft for inclusion in formats in future
versions of Excel. There's quite a few million users in India and we
have had quite a few requests for this format.

Don't hold your breath but if you don't ask, you don't get!