Display commas in general number format

[AutoFilterQueen]

Is it possible to display commas in the general number format? Here's what I'm trying to do. I have a worksheet in which a user will need to enter numerical values. If they enter 100 I would like it to display 100 if they enter 0.1234 I would like it to display 0.1234 if they enter 1234567 I would like it to display 1,234,567 if they enter 0.1 I would like it to display 0.1 Sorry, that was a run-on, but I didn't want to put in periods because I didn't want to confuse the numbers I was trying to show.

I like the general number format because it displays everything that is critical but no extra zeros. I am using the general number format now. But now the users want commas to be added. I experimented with custom number formats, with some success but 100 displayed as 100. and 0.1 displayed as .1 The problem is I don't know what type of number they are going to be entering, so I don't want to format it with a specific number of decimal places.

The data entry worksheet is dynamically created using VBA. I would be able to write code to assign the correct number format to each cell based on the number within it, but I think I would need to place the code in an event handler within the dynamically created worksheet. The VBA code is password protected as is the worksheet. I think this makes it difficult to reliably insert the code into the dynamically created worksheet (I think I would have to use Application.Sendkeys which I have found to be somewhat unreliable. Sorry for the long message but I'm trying to thoroughly explain what I'm trying to do. I'm hoping there is a simple solution, or should I just tell the users to live without their commas?

 

[Firefly2012]

You could consider using this as the custom number format:

[>100]#,##0;General

This clearly isn't perfect though

 

[AutoFilterQueen]

Thanks. That helped somewhat. I was able to get a little closer with

[>999]#,##0.###############;General

That displays my 1234.56 as 1234.56 instead of 1235 However, it still displays my 20,000 as 20,000. (in other words, it includes the decimal point on the end of 20,000 and I don't want it to.) Any other ideas on how to avoid showing the decimal point in an integer but still show the commas? I can't figure out any custom number format syntax I could use to test whether something is an integer. It seems to only accept = Equal to, > Greater than, < Less than, >= Greater than or equal to, <= Less than or equal to, and <> Not equal to.

Does anyone have any ideas of how show 20,000. without the decimal point but still show the other examples correctly?

 

[AutoFilterQueen]

Oops, in my last post I meant 1,234.56 and 1,235 but that is irrelevant to what I'm trying to accomplish now...