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!
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?
Hi, you need to enter the comma as a textual character (ie wih quotes) in the custom number format.
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.
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)
If Target.Cells.Count = 1 Then
If Target.Value >= 1000000 Then
Target.Cells.NumberFormat = "0"",""00"",""000.00"
Target.Cells.NumberFormat = "##,###.00"
For Each c In Target
If c.Value >= 1000000 Then
c.Cells.NumberFormat = "0"",""00"",""000.00"
c.Cells.NumberFormat = "##,###.00"