Indian Comma style

[Shital Shah]

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]

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

http://google.com/[email protected]

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

 

[David McRitchie]

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@tkmsftngp02
http://groups.google.com/[email protected]

 

[Shital]

Thanks for ur replay

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

Shital

-----Original Message-----
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
40NoMoreSpammodelfitness.com



"Shital Shah" <[email protected]> wrote in

message news:[email protected]...

 

[Norman Harker]

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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Thanks for ur replay

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

Shital

-----Original Message-----
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
40NoMoreSpammodelfitness.com



"Shital Shah" <[email protected]> wrote in

message news:[email protected]...

.

 

[David McRitchie]

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.

 

[Norman Harker]

Hi David!

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

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]

Dear sir,
Mr. Norman Harker

Thank you very much.
I'ts working fine.

thanks again

Shital shah

-----Original Message-----
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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Thanks for ur replay

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

Shital

-----Original Message-----
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/exc

el
/xlmain10/html/Bahttext.htm

http://groups.google.com/groups?selm=OwOZ4VaBBHA.1812% 40tkmsftngp02
http://groups.google.com/groups?

threadm=3B82C702.75F3C989%
40NoMoreSpammodelfitness.com

"Shital Shah" <[email protected]> wrote in

message news:[email protected]...

I want to use Indian Comma style upto 100 crores.

with -
ve

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]

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
(e-mail address removed)

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]

thanks for reply .

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

 

[Norman Harker]

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!

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)

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/