formatting rupees

B

BorisS

I need to custom format a cell to read 00,00,000, instead of the usual comma
after three spaces.

This is for an application that uses Rupees, which have a comma break at
1,000, then the next 100, and then another 100. In other words, whereas 10M
would normally print "10,000,000", I need it to print "100,00,000". Further,
I need every 1,000 after this to be at the three digit mark. In other words,
1B would need to print as "100,000,00,00,000". The key here is to get a
comma between the uptick from 99,000 to 100,000, so that it shows up
1,00,000; and the next comma to show between the uptick from 9,999,000 to
10,000,000, where the 10M should show up as 1,00,00,000.

I'd also ideally like to have "Rs." printed right before the number. If
anyone can give me the custom field text for formatting, would be greatly
appreciated.

Thx.
 
B

Bob Phillips

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

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
B

BorisS

Bob, thanks. I had this working the past few days, and then just came across
a negative value, which is not adhering to these formatting standards. Any
chance you can give me an update that would conquer that issue?

Thx.
--
Boris


Bob Phillips said:
[>=10000000]"Rs."##\,##\,##\,##0.00;[>=100000]"Rs."##\,##\,##0.00;"Rs."##,##
0.00

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

BorisS said:
I need to custom format a cell to read 00,00,000, instead of the usual comma
after three spaces.

This is for an application that uses Rupees, which have a comma break at
1,000, then the next 100, and then another 100. In other words, whereas 10M
would normally print "10,000,000", I need it to print "100,00,000". Further,
I need every 1,000 after this to be at the three digit mark. In other words,
1B would need to print as "100,000,00,00,000". The key here is to get a
comma between the uptick from 99,000 to 100,000, so that it shows up
1,00,000; and the next comma to show between the uptick from 9,999,000 to
10,000,000, where the 10M should show up as 1,00,00,000.

I'd also ideally like to have "Rs." printed right before the number. If
anyone can give me the custom field text for formatting, would be greatly
appreciated.

Thx.
 
B

Bob Phillips

I don't think that is possible as the format cannot handle that number of
conditions (2 for positive, 2 for negative and default). What you could do
is use event code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<=== change to suit
Const POS_FORMAT As String = _

"[>=10000000]""Rs.""##\,##\,##\,##0.00;[>=100000]""Rs.""##\,##\,##0.00;""Rs.
""##,##0.00"
Const NEG_FORMAT As String = _

"[<=-10000000]""-Rs.""##\,##\,##\,##0.00;[<=-100000]""-Rs.""##\,##\,##0.00;"
"Rs.""##,##0.00"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value >= 0 Then
.NumberFormat = POS_FORMAT
Else
.NumberFormat = NEG_FORMAT
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

BorisS said:
Bob, thanks. I had this working the past few days, and then just came across
a negative value, which is not adhering to these formatting standards. Any
chance you can give me an update that would conquer that issue?

Thx.
--
Boris


Bob Phillips said:
[>=10000000]"Rs."##\,##\,##\,##0.00;[>=100000]"Rs."##\,##\,##0.00;"Rs."##,##
0.00

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

BorisS said:
I need to custom format a cell to read 00,00,000, instead of the usual comma
after three spaces.

This is for an application that uses Rupees, which have a comma break at
1,000, then the next 100, and then another 100. In other words,
whereas
10M
would normally print "10,000,000", I need it to print "100,00,000". Further,
I need every 1,000 after this to be at the three digit mark. In other words,
1B would need to print as "100,000,00,00,000". The key here is to get a
comma between the uptick from 99,000 to 100,000, so that it shows up
1,00,000; and the next comma to show between the uptick from 9,999,000 to
10,000,000, where the 10M should show up as 1,00,00,000.

I'd also ideally like to have "Rs." printed right before the number. If
anyone can give me the custom field text for formatting, would be greatly
appreciated.

Thx.
 
B

BorisS

I've not had experience with worksheet code. I guess what I'm not clear on
is how this knows what to apply this formatting to. I don't want everything
to be this formatting.
--
Boris


Bob Phillips said:
I don't think that is possible as the format cannot handle that number of
conditions (2 for positive, 2 for negative and default). What you could do
is use event code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<=== change to suit
Const POS_FORMAT As String = _

"[>=10000000]""Rs.""##\,##\,##\,##0.00;[>=100000]""Rs.""##\,##\,##0.00;""Rs.
""##,##0.00"
Const NEG_FORMAT As String = _

"[<=-10000000]""-Rs.""##\,##\,##\,##0.00;[<=-100000]""-Rs.""##\,##\,##0.00;"
"Rs.""##,##0.00"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value >= 0 Then
.NumberFormat = POS_FORMAT
Else
.NumberFormat = NEG_FORMAT
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

BorisS said:
Bob, thanks. I had this working the past few days, and then just came across
a negative value, which is not adhering to these formatting standards. Any
chance you can give me an update that would conquer that issue?

Thx.
--
Boris


Bob Phillips said:
[>=10000000]"Rs."##\,##\,##\,##0.00;[>=100000]"Rs."##\,##\,##0.00;"Rs."##,##
0.00

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

I need to custom format a cell to read 00,00,000, instead of the usual
comma
after three spaces.

This is for an application that uses Rupees, which have a comma break at
1,000, then the next 100, and then another 100. In other words, whereas
10M
would normally print "10,000,000", I need it to print "100,00,000".
Further,
I need every 1,000 after this to be at the three digit mark. In other
words,
1B would need to print as "100,000,00,00,000". The key here is to get a
comma between the uptick from 99,000 to 100,000, so that it shows up
1,00,000; and the next comma to show between the uptick from 9,999,000 to
10,000,000, where the 10M should show up as 1,00,00,000.

I'd also ideally like to have "Rs." printed right before the number. If
anyone can give me the custom field text for formatting, would be greatly
appreciated.

Thx.
 
D

Dave Peterson

Bob gave instructions on how to insert the code with this:

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


And this is the line that should change to the range you want to use:

Const WS_RANGE As String = "H1:H10" '<=== change to suit

Just type the address you want in place of H1:H10.
I've not had experience with worksheet code. I guess what I'm not clear on
is how this knows what to apply this formatting to. I don't want everything
to be this formatting.
--
Boris

Bob Phillips said:
I don't think that is possible as the format cannot handle that number of
conditions (2 for positive, 2 for negative and default). What you could do
is use event code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<=== change to suit
Const POS_FORMAT As String = _

"[>=10000000]""Rs.""##\,##\,##\,##0.00;[>=100000]""Rs.""##\,##\,##0.00;""Rs.
""##,##0.00"
Const NEG_FORMAT As String = _

"[<=-10000000]""-Rs.""##\,##\,##\,##0.00;[<=-100000]""-Rs.""##\,##\,##0.00;"
"Rs.""##,##0.00"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value >= 0 Then
.NumberFormat = POS_FORMAT
Else
.NumberFormat = NEG_FORMAT
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

BorisS said:
Bob, thanks. I had this working the past few days, and then just came across
a negative value, which is not adhering to these formatting standards. Any
chance you can give me an update that would conquer that issue?

Thx.
--
Boris


:

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

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

I need to custom format a cell to read 00,00,000, instead of the usual
comma
after three spaces.

This is for an application that uses Rupees, which have a comma break at
1,000, then the next 100, and then another 100. In other words, whereas
10M
would normally print "10,000,000", I need it to print "100,00,000".
Further,
I need every 1,000 after this to be at the three digit mark. In other
words,
1B would need to print as "100,000,00,00,000". The key here is to get a
comma between the uptick from 99,000 to 100,000, so that it shows up
1,00,000; and the next comma to show between the uptick from 9,999,000 to
10,000,000, where the 10M should show up as 1,00,00,000.

I'd also ideally like to have "Rs." printed right before the number. If
anyone can give me the custom field text for formatting, would be greatly
appreciated.

Thx.
 
B

BorisS

so this is only for a specific range, as opposed to being something I can
apply as a regular, custom format. Am I understanding correctly?
--
Boris


Dave Peterson said:
Bob gave instructions on how to insert the code with this:

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


And this is the line that should change to the range you want to use:

Const WS_RANGE As String = "H1:H10" '<=== change to suit

Just type the address you want in place of H1:H10.
I've not had experience with worksheet code. I guess what I'm not clear on
is how this knows what to apply this formatting to. I don't want everything
to be this formatting.
--
Boris

Bob Phillips said:
I don't think that is possible as the format cannot handle that number of
conditions (2 for positive, 2 for negative and default). What you could do
is use event code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<=== change to suit
Const POS_FORMAT As String = _

"[>=10000000]""Rs.""##\,##\,##\,##0.00;[>=100000]""Rs.""##\,##\,##0.00;""Rs.
""##,##0.00"
Const NEG_FORMAT As String = _

"[<=-10000000]""-Rs.""##\,##\,##\,##0.00;[<=-100000]""-Rs.""##\,##\,##0.00;"
"Rs.""##,##0.00"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value >= 0 Then
.NumberFormat = POS_FORMAT
Else
.NumberFormat = NEG_FORMAT
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob, thanks. I had this working the past few days, and then just came
across
a negative value, which is not adhering to these formatting standards.
Any
chance you can give me an update that would conquer that issue?

Thx.
--
Boris


:


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

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

I need to custom format a cell to read 00,00,000, instead of the usual
comma
after three spaces.

This is for an application that uses Rupees, which have a comma break
at
1,000, then the next 100, and then another 100. In other words,
whereas
10M
would normally print "10,000,000", I need it to print "100,00,000".
Further,
I need every 1,000 after this to be at the three digit mark. In other
words,
1B would need to print as "100,000,00,00,000". The key here is to get
a
comma between the uptick from 99,000 to 100,000, so that it shows up
1,00,000; and the next comma to show between the uptick from 9,999,000
to
10,000,000, where the 10M should show up as 1,00,00,000.

I'd also ideally like to have "Rs." printed right before the number.
If
anyone can give me the custom field text for formatting, would be
greatly
appreciated.

Thx.
 
D

Dave Peterson

Yes.
so this is only for a specific range, as opposed to being something I can
apply as a regular, custom format. Am I understanding correctly?
--
Boris

Dave Peterson said:
Bob gave instructions on how to insert the code with this:

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


And this is the line that should change to the range you want to use:

Const WS_RANGE As String = "H1:H10" '<=== change to suit

Just type the address you want in place of H1:H10.
I've not had experience with worksheet code. I guess what I'm not clear on
is how this knows what to apply this formatting to. I don't want everything
to be this formatting.
--
Boris

:

I don't think that is possible as the format cannot handle that number of
conditions (2 for positive, 2 for negative and default). What you could do
is use event code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<=== change to suit
Const POS_FORMAT As String = _

"[>=10000000]""Rs.""##\,##\,##\,##0.00;[>=100000]""Rs.""##\,##\,##0.00;""Rs.
""##,##0.00"
Const NEG_FORMAT As String = _

"[<=-10000000]""-Rs.""##\,##\,##\,##0.00;[<=-100000]""-Rs.""##\,##\,##0.00;"
"Rs.""##,##0.00"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value >= 0 Then
.NumberFormat = POS_FORMAT
Else
.NumberFormat = NEG_FORMAT
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob, thanks. I had this working the past few days, and then just came
across
a negative value, which is not adhering to these formatting standards.
Any
chance you can give me an update that would conquer that issue?

Thx.
--
Boris


:


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

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

I need to custom format a cell to read 00,00,000, instead of the usual
comma
after three spaces.

This is for an application that uses Rupees, which have a comma break
at
1,000, then the next 100, and then another 100. In other words,
whereas
10M
would normally print "10,000,000", I need it to print "100,00,000".
Further,
I need every 1,000 after this to be at the three digit mark. In other
words,
1B would need to print as "100,000,00,00,000". The key here is to get
a
comma between the uptick from 99,000 to 100,000, so that it shows up
1,00,000; and the next comma to show between the uptick from 9,999,000
to
10,000,000, where the 10M should show up as 1,00,00,000.

I'd also ideally like to have "Rs." printed right before the number.
If
anyone can give me the custom field text for formatting, would be
greatly
appreciated.

Thx.
 
B

Bob Phillips

Yes, but you can keep adding ranges to the target range, such as

Const WS_RANGE As String = "H1:H10,M1:M5,O17"

etc.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

BorisS said:
so this is only for a specific range, as opposed to being something I can
apply as a regular, custom format. Am I understanding correctly?
--
Boris


Dave Peterson said:
Bob gave instructions on how to insert the code with this:

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


And this is the line that should change to the range you want to use:

Const WS_RANGE As String = "H1:H10" '<=== change to suit

Just type the address you want in place of H1:H10.
I've not had experience with worksheet code. I guess what I'm not clear on
is how this knows what to apply this formatting to. I don't want everything
to be this formatting.
--
Boris

:

I don't think that is possible as the format cannot handle that number of
conditions (2 for positive, 2 for negative and default). What you could do
is use event code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<=== change to suit
Const POS_FORMAT As String = _

"[>=10000000]""Rs.""##\,##\,##\,##0.00;[>=100000]""Rs.""##\,##\,##0.00;""Rs.
""##,##0.00"
Const NEG_FORMAT As String = _

"[<=-10000000]""-Rs.""##\,##\,##\,##0.00;[<=-100000]""-Rs.""##\,##\,##0.00;"
"Rs.""##,##0.00"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value >= 0 Then
.NumberFormat = POS_FORMAT
Else
.NumberFormat = NEG_FORMAT
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob, thanks. I had this working the past few days, and then just came
across
a negative value, which is not adhering to these formatting standards.
Any
chance you can give me an update that would conquer that issue?

Thx.
--
Boris


:


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

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

I need to custom format a cell to read 00,00,000, instead of the usual
comma
after three spaces.

This is for an application that uses Rupees, which have a comma break
at
1,000, then the next 100, and then another 100. In other words,
whereas
10M
would normally print "10,000,000", I need it to print "100,00,000".
Further,
I need every 1,000 after this to be at the three digit mark. In other
words,
1B would need to print as "100,000,00,00,000". The key here is to get
a
comma between the uptick from 99,000 to 100,000, so that it shows up
1,00,000; and the next comma to show between the uptick from 9,999,000
to
10,000,000, where the 10M should show up as 1,00,00,000.

I'd also ideally like to have "Rs." printed right before the number.
If
anyone can give me the custom field text for formatting, would be
greatly
appreciated.

Thx.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

custom format 0
custom formatting 0
formatting for Rupees 0
Custom Format for Numbers 4
custom formatting 0
Comma format for numbers in Excel 1
custom formatting not working 0
Custom Formatting (# x 1000) 2

Top