0

Is there any way to format numbers in 123/Excel-compatible spreadsheet applications, Google Spreadsheets in particular, in a way that hides the fractional part and the preceding decimal separator (i.e. dot or comma depending on locale) if it is zero (i.e. the number is an integer)?

123

  • 0.0 → 123.0
  • 0.# → 123. this is the closest I get
  • 0.? → 123.␠
  • 0_.0 → 123 0
  • 0_.# → 123 
  • 0_.? → 123 ␠

1.23

  • 0.0% → 123.0%
  • 0.#% → 123.%
  • 0.?% → 123. %
  • 0_.0% → 123 0%
  • 0_.#% → 123 %
  • 0_.?% → 123  %

I know that one can format positive, negative and zero-value numbers separately with semicolons: +0.0%;−0.0%;±0_.?%

  • Possible duplicate of Excel number format adds decimal point for whole numbers – Tom Brunberg May 23 '19 at 7:35
  • 1
    Apparently, I used the wrong search terms and the answer is “itʼs not possible with number formatting character codes alone, but together with conditional formatting if formulas, ROUND() in particular, are supported therein”. – Crissov May 23 '19 at 7:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.