2

I'm trying to mimic the way a "general" cell shows a decimal point only if there are decimal places, but I want thousands separators which "general" doesn't provide. I'm using a custom number format string in a SSRS report: #,##0.######. When the report is exported to Excel, the format string comes through as [$-en-US,1]#,##0.######. Removing the locale specification makes no difference to my problem.

This works except for one thing: Excel always displays a decimal point even when there are no decimal places. Given the value 1000.1, it displays 1,000.1 as I intended. But given the value 10, it displays 10. instead of 10.

Is it possible to achieve what I want?

I created a .xlsx file with the format string #,0.### on two cells. This is how the same file looks in Excel and Calc:

enter image description here

StackOverthrow
  • 123
  • 1
  • 8
  • Check if any of the answers here https://superuser.com/questions/205759/format-a-number-with-optional-decimal-places-in-excel are useful. – patkim Jul 20 '18 at 21:21
  • 1
    @pat2015 That's the weird thing. [This answer](https://superuser.com/a/328143/886580) suggests `0.####` as a custom format that will render the asker's `15` as `15`. But I get `15.`. – StackOverthrow Jul 20 '18 at 21:28
  • @TKK, the thumb rule is, if you apply Format to Cells Excel will apply it on every value whether you need or not. Your question Title is "Conditional display decimal(not place)", is quite confusing !! If you want to apply Decimal format to the Range but conditionally, then what is your criteria? – Rajesh Sinha Jul 21 '18 at 07:32
  • You could use a formula and the TEXT function to do it, but that's probably overkill. – jeffreyweir Jul 21 '18 at 11:55
  • @RajeshS I want the decimal point to be hidden when there are no significant decimal places, as shown in the screenshot from Calc. SSRS also works the way I expect. I think this is a bug in Excel. – StackOverthrow Jul 23 '18 at 16:39
  • @jeffreyweir I can easily make it look the way I want if I use `format(...)` in my report, but I want the cells to be numeric, not text. – StackOverthrow Jul 23 '18 at 16:40

1 Answers1

0

You could use a conditional format that uses #,0 when the value is a whole number. First set the cells to use the format #,0.###### and then add a conditional format based on a formula and use =MOD(A1, 1) = 0 where A1 is the first cell in the range. Barring any weird rounding errors, it will return TRUE when the number has no fraction and FALSE otherwise.

Jason Clement
  • 1,988
  • 2
  • 16
  • 21