cell format

[pbchhaya]

default cell format is like 100,000 now i would like to show my no. as 1,00,000 or if numirc with 2 dgt then 1,00,000.00. I am unable to do so. can any one help.

pbchhaya

 

[Iridium]

Welcome to the Board!

Not sure but you could try changing your 'Regional and Language options' from the control panel to do what you want. Else it's a custom cell format which I've never been any good at so I can't help you with that - sorry!

HTH

 

[MartinK]

Hello,
why would you want to do this?
I am afraid this is not so easy (not possible?) through custom format. You can do many tricks with displaying in thousands, etc., but floating hunderds/thousands separator? I don't know.
For just displaying the result like this, you might convert the numer to string, e.g. =TEXT(number,"0.00") and insert the commas into the string using concatenation or Substitute function. But what about numbers > 999 999? How would you like to treat these?
Martin

 

[Iridium]

"Why would you want to do that?"

Well I have a feeling (though not sure) that it's to do with the Indian currency system (lakhs etc) hence the OP's request (though I may be wrong)

???

 

[MartinK]

Whoops, I am looking forward to learn something new then

Anyway, I am curious to see if someone comes up with an idea for custom format...

Cheers
Martin

 

[Iridium]

Whoops, I am looking forward to learn something new then

Anyway, I am curious to see if someone comes up with an idea for custom format...

Cheers
Martin

No "whoops" necessary - I'm intrigued as you are - I was just having a very slightly informed guess!

 

[parry]

Hi, you need to enter the comma as a textual character (ie wih quotes) in the custom number format.

0","00","000.00

EDIT: I am going to have to rethink this as its not very good for amounts < 1 million. I think you will have to use code instead to change the customer number format on the fly based on the value. I'll write something that will do this.

 

[parry]

Hi again, heres a procedure that will do this for you. Right click the sheet, select View Code then paste in the right hand window pane (big white area).

I dont know what happens with your thousands separator for numbers higher than 1 million so it may look funny if you put in real high numbers. This can be fixed if you tell me the format for the next series of numbers.

The procedure reformats the cell whenever you make any change to a cell in that sheet so if you only want this format limited to certain columns etc please let me know.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c
If Target.Cells.Count = 1 Then
    If Target.Value >= 1000000 Then
        Target.Cells.NumberFormat = "0"",""00"",""000.00"
    Else
        Target.Cells.NumberFormat = "##,###.00"
    End If
Else
    For Each c In Target
    If c.Value >= 1000000 Then
        c.Cells.NumberFormat = "0"",""00"",""000.00"
    Else
        c.Cells.NumberFormat = "##,###.00"
    End If
    Next c
End If
End Sub

hth

 

[Yogi Anand]

default cell format is like 100,000 now i would like to show my no. as 1,00,000 or if numirc with 2 dgt then 1,00,000.00. I am unable to do so. can any one help.

pbchhaya

Hi pbchhaya:

Welcome to MrExcel Board!

In the following illustration, I have used the TEXT function to present integers with upto 15 digits to be presented according to India system of presentation ...

Book2
	B	C	D	E
1	Number	Numberofupto15digits formatedforIndiasystemofpresentation
2	1000000000	1,00,00,00,000
3	1000	1,000
4	10000	10,000
5	100000	1,00,000
Sheet4 (2)

Would this do? If you need to discuss this further, please post back and then let us take it from there.

 

[Yogi Anand]

And here is an extension ... coercing text formated numbers back to true numbers ...

y040131h1a.xls
	B	C	D	E
1	Number	Numberofupto15digits textformatedforIndiasystem ofpresentation	coercingtextformated backtotruenumber
2	1000000000	1,00,00,00,000	1000000000
3	1000	1,000	1000
4	10000	10,000	10000
5	100000	1,00,000	100000
Sheet4 (2)