4

Is there a way to use a custom number format in Google Sheets so that decimal point will be visible only when decimals are present? For instance

9
8,2
10

Is there a newer solution about achieving this without a helper column?

EDIT

While looking for a solution to the original question, a glitch was discovered in the sheet (Link here):

enter image description here

column AJ is formatted with number format 0.#, as this was obtained running the following Google Apps Script:

function test(){ 
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); 
var cell = sheet.getRange("AJ7"); 
Logger.log(cell.getNumberFormat());}

Values appear without decimals, when no decimals are present, however if this format is manually applied, it will show integer values with comma. How can it be? Locale is set to Italy, timezone GMT+1.

Improve this question
3
  • According to the comments to one of the answers, there is something "weird" happening. Please edit the question to add the link to your spreadsheet and also add more details like the spreadsheet settings like the locale is set to Italy, the timezone to Berlin.
    – Rubén ♦
    Dec 1, 2019 at 19:07
  • Edited...............
    – Riccardo
    Dec 1, 2019 at 19:26
  • I updated my answer to replace the proposed workaround for something simpler
    – Rubén ♦
    Dec 1, 2019 at 20:39

4 Answers 4

Reset to default
1

Simplest workaround if you want to display to say 1 decimal place but remove trailing zeros after the decimal point is to use the formula =int(3.14159265*10)/10 with number formatting set to Automatic.

Improve this answer
0

Using 'Automatic' or 'Plain Text' format usually does the behavior you are describing. If you try to format a number that way you will end up with a . after a whole number so pretty much you need to have a helper column. The closest you can get with using only formatting is to do 0.0### which will always show 1 decimal place but expand out 4 decimal places if necessary. If you want you can delete or add more #

Improve this answer
8
  • Automatic format will use too many decimals........
    – Riccardo
    Nov 14, 2019 at 14:23
  • 1
    See my edit, you can control how many decimal places you have but it will require you to have at least 1 decimal place for everything.
    – CodeCamper
    Nov 14, 2019 at 14:31
  • See this document docs.google.com/spreadsheets/d/… cell in AJ7 is showing the value without decimals. Number format is 0.# but if I manually apply this again, it will show integer values with comma. How can it be? I got the original format running this script` function deporva(){ var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); //var sheet = ss.getSheets()[0]; var cell = sheet.getRange("AJ7"); Logger.log(cell.getNumberFormat()); }`
    – Riccardo
    Nov 15, 2019 at 11:04
  • @Riccardo Maybe they used an import excel glitch to force in an unauthorized format? Can you ask the owner how they did it?
    – CodeCamper
    Nov 18, 2019 at 5:22
  • 1
    @Riccardo you should ask a new question with that spreadsheet because honestly it is a mystery and appears to be a glitch.
    – CodeCamper
    Nov 18, 2019 at 6:21
0

If your number is 3.14159265 you will get 3.14159265, if you want less there is no way to have 3.14 and have no ".00" in whole numbers. You cannot achieve it with no auxiliary column where you use trunk(A2,2) and then use automatic in the format for the result

I just changed the format of column J in your example file to "automatic" and numbers are shown as you expected them

Improve this answer
1
  • See the comments below Codecamper's answer. There's a glitch
    – Riccardo
    Dec 1, 2019 at 18:30
0

The same occurs in Excel Ref Excel number format to only show decimals when necessary, unfortunatelly the conditional formatting options in Google Sheets don't include numbering formatting.

What about this simple workaround?

  1. Set number formatting to Automatic
  2. Add and empty string to the result of the original formula:

=""&if(F7+J7+O7+S7+Y7+AD7+AH7;average(if(F7;F7);if(J7;J7);if(O7;O7);if(S7;S7);if(Y7;Y7);if(AD7;AD7);if(AH7;AH7));"")

Regarding if this is a bug, consider to report it to Google by using Google Feedback.

Improve this answer
5
  • Any idea why the formatting works in that spreadsheet he added a link to? Even when I add my locale to USA and change the timezone it seems the formatting works (only when copy and pasting or you can copy formatting), at first I suspected it was an import glitch with LibreOffice since it has this sort of "optional" default behavior but I couldn't get it working from scratch.
    – CodeCamper
    Dec 3, 2019 at 3:23
  • @CodeCamper I have "many" "ideas" but I think that most of them are not helpful at all.
    – Rubén ♦
    Dec 3, 2019 at 3:42
  • 1
    It's a shame we can't reproduce it, now I have to create a google sheet called exotic formatting and copy from it when I want decimal formatting like this...
    – CodeCamper
    Dec 3, 2019 at 3:51
  • Actually, maybe the "explanation" is that Google took the OpenOffice code but they didn't include all the menus/options in the Google Sheets UI Ref. support.google.com/docs/table/25273?hl=en (see the bottom of the page, just below the list of functions)
    – Rubén ♦
    Dec 3, 2019 at 4:01
  • I think you found it, there is a setting in OpenOffice and LibreOffice to handle this kind of situation, interestingly copy formatting is copying this data, now just how to access it... I tried importing a LibreOffice with the setting but it didn't work for me.
    – CodeCamper
    Dec 3, 2019 at 5:28

Your Answer

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

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