2

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.

  • 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 at 19:07
  • Edited............... – Riccardo Dec 1 at 19:26
  • I updated my answer to replace the proposed workaround for something simpler – Rubén Dec 1 at 20:39
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 #

  • Automatic format will use too many decimals........ – Riccardo Nov 14 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 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 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 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 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

  • See the comments below Codecamper's answer. There's a glitch – Riccardo Dec 1 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.

  • 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 2 days ago
  • @CodeCamper I have "many" "ideas" but I think that most of them are not helpful at all. – Rubén 2 days ago
  • 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 2 days ago
  • 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 2 days ago
  • 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 2 days ago

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.