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

Improve this question
6
  • Check if any of the answers here superuser.com/questions/205759/… are useful.
    – patkim
    Commented Jul 20, 2018 at 21:21
  • 1
    @pat2015 That's the weird thing. This answer suggests 0.#### as a custom format that will render the asker's 15 as 15. But I get 15..
    – StackOverthrow
    Commented Jul 20, 2018 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
    Commented Jul 21, 2018 at 7:32
  • You could use a formula and the TEXT function to do it, but that's probably overkill.
    – jeffreyweir
    Commented Jul 21, 2018 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
    Commented Jul 23, 2018 at 16:39

1 Answer 1

Reset to default
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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .