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 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
  • 2,098