7

I'm trying to format a number in Google Spreadsheets. I want it to show the thousands comma, then a period ., followed by mostly 3 digits, only when necessary.

For example:

  • 1,234.56789 => 1,234.568
  • 1,234.5 => 1,234.5
  • 1,234 => 1,234
  • 1,234.00 => 1,234

I tried #,#.#, but then the period shows up always. #,#.0 doesn't omit the decimal point when its value is zero.

3

closest you can get is to use 1+ column

  • go to 123 or Format
  • select Numbers
  • select More formats
  • select Custom number format...
  • type: #,###.###
  • and corect it with: =IF(RIGHT(A2;1)=".";SUBSTITUTE(A2;".";"");A2)

https://docs.google.com/spreadsheets/d/

3
  • Thanks for the update. Is there a way to correct the dot without a formula? Jul 22 '18 at 13:01
  • 1
    @Shimmy not rly, because it would then violate your last rule "followed by mostly 3 digits, only when necessary"
    – user0
    Jul 22 '18 at 18:44
  • 1
    wtf this is awful. Is there a way to do that (without the last 3 digit rule asked by OP)
    – wischi
    Sep 30 '20 at 9:14

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.