|
Commas in indian style
Hi!
sample number 123456789 In excel normally numbers are seprated in 1000's format ie 123,456,789.00 I NEED IT IN this format (indian style of rupees) 12,34,56,789 |
Commas in indian style
Use a custom format of
[=10000000]##\,##\,##\,##0;[=100000]##\,##\,##0.00;##,##0 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "SVC" wrote in message ... Hi! sample number 123456789 In excel normally numbers are seprated in 1000's format ie 123,456,789.00 I NEED IT IN this format (indian style of rupees) 12,34,56,789 |
Commas in indian style
How to add decimial points & can you explain this sequence of hash & zeros
"Bob Phillips" wrote in message ... Use a custom format of [=10000000]##\,##\,##\,##0;[=100000]##\,##\,##0.00;##,##0 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "SVC" wrote in message ... Hi! sample number 123456789 In excel normally numbers are seprated in 1000's format ie 123,456,789.00 I NEED IT IN this format (indian style of rupees) 12,34,56,789 |
Commas in indian style
Decimal points
[=10000000]##\,##\,##\,##0.00;[=100000]##\,##\,##0.00;##,##0.00 All the hashes represent a number, but show as blank if they are leading zeroes. With normal numbers, you just define #,##0.00 and Excel will add a comma every 3rd digit, but as Indian numbers have commas in 2nd digit in some cases, it all has to be explicitly defined. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "SVC" wrote in message ... How to add decimial points & can you explain this sequence of hash & zeros "Bob Phillips" wrote in message ... Use a custom format of [=10000000]##\,##\,##\,##0;[=100000]##\,##\,##0.00;##,##0 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "SVC" wrote in message ... Hi! sample number 123456789 In excel normally numbers are seprated in 1000's format ie 123,456,789.00 I NEED IT IN this format (indian style of rupees) 12,34,56,789 |
Commas in indian style
Dear Phillips,
my requirement is as below no matter what the number is it should display 2 decimal places & then going from left to right 1st 3 digits left alone after which there should be comma after every 2 digits. eg: 100 = 100.00 1000 = 1,000.00 10000 = 10,000.00 100000 = 1,00,000.00 1000000 = 10,00,000.00 10000000 = 1,00,00,000.00 100000000 = 10,00,00,000.00 1000000000 = 1,00,00,00,000.00 10000000000 = 10,00,00,00,000.00 ...... & so on "Bob Phillips" wrote in message ... Decimal points [=10000000]##\,##\,##\,##0.00;[=100000]##\,##\,##0.00;##,##0.00 All the hashes represent a number, but show as blank if they are leading zeroes. With normal numbers, you just define #,##0.00 and Excel will add a comma every 3rd digit, but as Indian numbers have commas in 2nd digit in some cases, it all has to be explicitly defined. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "SVC" wrote in message ... How to add decimial points & can you explain this sequence of hash & zeros "Bob Phillips" wrote in message ... Use a custom format of [=10000000]##\,##\,##\,##0;[=100000]##\,##\,##0.00;##,##0 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "SVC" wrote in message ... Hi! sample number 123456789 In excel normally numbers are seprated in 1000's format ie 123,456,789.00 I NEED IT IN this format (indian style of rupees) 12,34,56,789 |
Commas in indian style
That is difficult, because the format only allows a certain number of
conditions, and we have exhausted them. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "SVC" wrote in message ... Dear Phillips, my requirement is as below no matter what the number is it should display 2 decimal places & then going from left to right 1st 3 digits left alone after which there should be comma after every 2 digits. eg: 100 = 100.00 1000 = 1,000.00 10000 = 10,000.00 100000 = 1,00,000.00 1000000 = 10,00,000.00 10000000 = 1,00,00,000.00 100000000 = 10,00,00,000.00 1000000000 = 1,00,00,00,000.00 10000000000 = 10,00,00,00,000.00 ..... & so on "Bob Phillips" wrote in message ... Decimal points [=10000000]##\,##\,##\,##0.00;[=100000]##\,##\,##0.00;##,##0.00 All the hashes represent a number, but show as blank if they are leading zeroes. With normal numbers, you just define #,##0.00 and Excel will add a comma every 3rd digit, but as Indian numbers have commas in 2nd digit in some cases, it all has to be explicitly defined. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "SVC" wrote in message ... How to add decimial points & can you explain this sequence of hash & zeros "Bob Phillips" wrote in message ... Use a custom format of [=10000000]##\,##\,##\,##0;[=100000]##\,##\,##0.00;##,##0 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "SVC" wrote in message ... Hi! sample number 123456789 In excel normally numbers are seprated in 1000's format ie 123,456,789.00 I NEED IT IN this format (indian style of rupees) 12,34,56,789 |
Commas in indian style
Is there any macro through which we can achive the same.
"Bob Phillips" wrote in message ... That is difficult, because the format only allows a certain number of conditions, and we have exhausted them. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "SVC" wrote in message ... Dear Phillips, my requirement is as below no matter what the number is it should display 2 decimal places & then going from left to right 1st 3 digits left alone after which there should be comma after every 2 digits. eg: 100 = 100.00 1000 = 1,000.00 10000 = 10,000.00 100000 = 1,00,000.00 1000000 = 10,00,000.00 10000000 = 1,00,00,000.00 100000000 = 10,00,00,000.00 1000000000 = 1,00,00,00,000.00 10000000000 = 10,00,00,00,000.00 ..... & so on "Bob Phillips" wrote in message ... Decimal points [=10000000]##\,##\,##\,##0.00;[=100000]##\,##\,##0.00;##,##0.00 All the hashes represent a number, but show as blank if they are leading zeroes. With normal numbers, you just define #,##0.00 and Excel will add a comma every 3rd digit, but as Indian numbers have commas in 2nd digit in some cases, it all has to be explicitly defined. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "SVC" wrote in message ... How to add decimial points & can you explain this sequence of hash & zeros "Bob Phillips" wrote in message ... Use a custom format of [=10000000]##\,##\,##\,##0;[=100000]##\,##\,##0.00;##,##0 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "SVC" wrote in message ... Hi! sample number 123456789 In excel normally numbers are seprated in 1000's format ie 123,456,789.00 I NEED IT IN this format (indian style of rupees) 12,34,56,789 |
Commas in indian style
Maybe...
If the numbers change because of user input, you could use an event macro. Rightclick on the worksheet tab that should have this behavior and select view code. Paste this in: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myNumberFormat As String If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("b:b")) Is Nothing Then Exit Sub If Application.IsNumber(Target.Value) = False Then Exit Sub myNumberFormat = "" Select Case Target.Value Case Is < 100000 myNumberFormat = "#,###.00" Case Is < 1000000 myNumberFormat = "##\,##\,##0.00" Case Is < 100000000 myNumberFormat = "##\,##\,##\,##0.00" End Select If myNumberFormat = "" Then 'you didn't make enough formats! Else Target.NumberFormat = myNumberFormat End If End Sub Add as many checks as you want. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm If you want to read more about these kinds of events: Chip Pearson's site: http://www.cpearson.com/excel/events.htm David McRitchie's site: http://www.mvps.org/dmcritchie/excel/event.htm SVC wrote: Is there any macro through which we can achive the same. "Bob Phillips" wrote in message ... That is difficult, because the format only allows a certain number of conditions, and we have exhausted them. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "SVC" wrote in message ... Dear Phillips, my requirement is as below no matter what the number is it should display 2 decimal places & then going from left to right 1st 3 digits left alone after which there should be comma after every 2 digits. eg: 100 = 100.00 1000 = 1,000.00 10000 = 10,000.00 100000 = 1,00,000.00 1000000 = 10,00,000.00 10000000 = 1,00,00,000.00 100000000 = 10,00,00,000.00 1000000000 = 1,00,00,00,000.00 10000000000 = 10,00,00,00,000.00 ..... & so on "Bob Phillips" wrote in message ... Decimal points [=10000000]##\,##\,##\,##0.00;[=100000]##\,##\,##0.00;##,##0.00 All the hashes represent a number, but show as blank if they are leading zeroes. With normal numbers, you just define #,##0.00 and Excel will add a comma every 3rd digit, but as Indian numbers have commas in 2nd digit in some cases, it all has to be explicitly defined. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "SVC" wrote in message ... How to add decimial points & can you explain this sequence of hash & zeros "Bob Phillips" wrote in message ... Use a custom format of [=10000000]##\,##\,##\,##0;[=100000]##\,##\,##0.00;##,##0 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "SVC" wrote in message ... Hi! sample number 123456789 In excel normally numbers are seprated in 1000's format ie 123,456,789.00 I NEED IT IN this format (indian style of rupees) 12,34,56,789 -- Dave Peterson |
| All times are GMT +1. The time now is 09:58 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com