Commas in indian style

[SVC]

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

 

[Bob Phillips]

Use a custom format of

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[SVC]

How to add decimial points & can you explain this sequence of hash & zeros

Use a custom format of

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

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

 

[Bob Phillips]

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)

How to add decimial points & can you explain this sequence of hash & zeros

Use a custom format of

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

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

 

[SVC]

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

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)

How to add decimial points & can you explain this sequence of hash &
zeros

Use a custom format of

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

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

 

[Bob Phillips]

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)

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

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)

How to add decimial points & can you explain this sequence of hash &
zeros

Use a custom format of

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

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

 

[SVC]

Is there any macro through which we can achive the same.

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)

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

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)

How to add decimial points & can you explain this sequence of hash &
zeros

Use a custom format of

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

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]

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

Is there any macro through which we can achive the same.

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)

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
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)

How to add decimial points & can you explain this sequence of hash &
zeros

Use a custom format of

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

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