CODEWITHSUNDEEP
google-sheets
dbzix
dbzix

Reputation: 166

Remove trailing ".00" from numbers in Google Sheets

In Google Sheets I'm trying to format numbers to have format "0.##" - so that integer be like integer (12 be exactly 12), decimals be like decimals with no trailing zeroes (12.30 be 12.3 and 12.4321 be 12.43), but in reality for 12 I've got 12. with this annoying decimal point and see NO WAY to get rid of it.

enter image description here

The same time if I choose Format -> Number -> Automatic, I've got calculated numbers be like 131.3666667 which is not my desired format.

In LibreOffice using format "0.##" removes unnecessary decimal point, but Google Sheets don't. If you know how to do it, please share your knowledge. Googling doesn't help much.

Thank you in advance!

Upvotes: 7

Views: 6132

Answers (4)

Jorge Galvão
Jorge Galvão

Reputation: 1893

If you don't use the result of these cells for further calculation (or if you do use, but don't mind losing some precision), you can surround your current value with the formula Round(<value>, 2) and don't use any special format for the cell(s), leave the format as automatic.

Some examples of what original numbers will appear as:

1.004 -> 1
1.006 -> 1.01
1.096 -> 1.1

Upvotes: 0

user23450893
user23450893

Reputation: 1

my google sheet .atuomatically adds .00 after any number . .enter image description here

Upvotes: -2

Nikko J.
Nikko J.

Reputation: 5543

Number format 0.## works well with Google Apps Script. It does not leave dot to numbers that has no decimals.

The code below will apply number formatting to the whole Sheet.

Try this:

function formatColumn() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");
  var range = sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns());
  range.setNumberFormat("0.##");
}

*Note:

  • You can change the range to column specific by changing the value of getRange(). Example: sheet.getRange(A1:A).
  • If you choose to format the whole sheet, you only need to run the script once to apply the format.

Output:

Before:

enter image description here

After:

enter image description here

Since we set the format to the whole sheet, It will automatically format any inputted numbers.

enter image description here

Reference:

Upvotes: 2

Mr Dream
Mr Dream

Reputation: 138

hi i found the Solution :

For Example let us say that you have the number 100 in the cell A1 ,

and you have the Number 99.9 in the cell B1 .

let us say you want to get the sum of them without having the.9 .

click on the cell that you want to add the sum on it :

let us say it is the cell C1 click on it then add =INT //(MUST BE CAPITAL) :

for example :

= INT(A1 + B1)

then hit Enter .

now the value of the sum is :

199

Upvotes: -2

Related Questions