14

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.

2 Answers 2

5

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/

6
  • 2
    Thanks for the update. Is there a way to correct the dot without a formula? Jul 22, 2018 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, 2018 at 18:44
  • 5
    wtf this is awful. Is there a way to do that (without the last 3 digit rule asked by OP)
    – wischi
    Sep 30, 2020 at 9:14
  • But this converts the number to a string, my format was [$₹][>9999999]##\,##\,##\,##0.00;[$₹][>99999]##\,##\,##0.00;[$₹]##,##0.#### even by using value function I am not able to convert it to number format please help! Nov 29, 2023 at 16:01
  • @AashitGarodia see perhaps stackoverflow.com/a/69773824/5632629
    – user0
    Nov 29, 2023 at 18:05
2

I know this won't resolve anything for most people, but for me the answer was to format the number as text in Excel before I copied it into Google sheets. Again, probably not useful, but for those of us who got it into Google this way, it might be helpful.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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