Decimal Points in Custom Number Formats

[jqavins]

I'd like to have a custom format in which the decimal point is handled the way "General" handles it, i.e. the point is there if and only if there is one or more digit to come after.

In particular, I want to add thousands separators (commas) without changing the decimals behavior. I've made a few attempts. Of course I can get the number of decimal places to grow as needed (#,###.####) but the dot still appears even for an integer.

Before anyone says it, yes, I can do this with conditional formatting. One should not have to do that, since this is really only a number formatting matter, and conditional formatting would be a workaround.

[MrShorty]

Before anyone says it, yes, I can do this with conditional formatting. One should not have to do that, since this is really only a number formatting matter, and conditional formatting would be a workaround.

As far as I know, Excel's number formatting has always behaved this way. Other than General, there is no number format code that makes the decimal point behave the way you describe you want it. The closest number format code is using the # after the decimal point like the #,###.#### you've already tried. As you note, Excel will display the decimal point even for integers. As far as I know, conditional formatting (that tests for the integer cases) is the only way of getting Excel to not show the decimal point. You can tell us that it shouldn't be necessary, but you really need to tell Excel's programmers that because it is up to them to change how Excel treats the decimal point in integers in that kind of number format.

Are you restricted to Excel? I tried your number format code on some random data in LO Calc (I'm currently running version 6.4). LO Calc behaved the way you want it -- integers did not display the decimal point. I also tried Google Sheets, but it behaved the same as Excel -- displaying the decimal point for integers. If this is important enough, switching to LO Calc will resolve this particular issue around decimal points.

I'm sorry that I don't have any good suggestions. If Excel is important to this, maybe keep up to date on your Excel updates and see if/when MSFT chooses to change this behavior in Excel.