Excel Formula To Remove Numbers After Commas

4 mins read
Please go to your post editor > Post Settings > Post Formats tab below your editor to enter video URL.

Last Updated on June 10, 2022 by Stanley Sanchez

In this excel tutorial, we will discuss how to remove the numbers behind the comma in excel decimal numbers. Namely rounding numbers to certain integer values ​​or integers in excel.

I often find people calling it a way to eliminate rounding numbers in excel.

In addition to using the rounding function, specifically, excel has provided several other functions or formulas to remove numbers after commas or decimal fraction values ​​in excel.

Of course, eliminating the number behind the comma referred to here is not just the appearance, while the value is still a decimal number. But actually, remove the number after the comma so that the value or value of the number becomes an integer or integer.

How to Set Decimal Numbers in Excel

How to set the decimal numbers in excel?

You can use cell formatting to increase or decrease the number of digits after the decimal point. This can also be done using the menu button Increase decimal or Decrease decimal which you can find on . For example, to remove commas in excel or display .00 in excel.Tab HomeGroup Number

If you intend to completely eliminate the decimal value of the number after the comma, then you need to use the excel formula. You can use the rounding formula or use some of the excel functions which we will discuss below.

Excel functions that are intended to remove numbers after commas so that the decimal number becomes an integer or an integer is to use the INT TRUNC ODD , and EVEN functions .

How to use these functions? Please see the explanation below.

Excel INT Functions/Formulas

The INT function is an excel function/formula that is used to round a number down to the nearest integer/integer.

How to Use Excel’s INT Function

How to use the INT function/formula in excel formulas are as follows:



The number argument in Excel’s Int function is the number you want to round down to the nearest integer.

INT Formula Example

An example of using the INT function is as follows:

By using the INT function, the value of a number will always be rounded down, as well as a number or minus number. So the integer value can be different from the positive value.

As in the examples of INT formulas number 4 and 5 above, the number -120.467 will be removed from the number behind the comma to -121 so that it becomes a negative integer. While the decimal number -1255,789 is rounded up to -1256.

Excel TRUNC Functions/Formulas

The TRUNC function is an excel function that is used to cut a decimal number into an integer by removing the decimal fraction number or the value after the comma from the number.

In excel formulas, the INT and TRUNC functions both turn decimal numbers into integers (without the numbers behind the comma), only the INT formula will round the number down to the nearest integer value while the TRUNC formula immediately cuts the number behind the comma without rounding.

How to Use the TRUNC Function

How to use the TRUNC function in excel formulas follows the following writing rules:


TRUNC(Number, [ManyDigits])

The Number argument is filled with the decimal number that we will cut or remove the fractional number. While the ManyDigit argument is filled with the number of digits of the truncation precision (Truncate) of the number.

The MultiDigit argument in the TRUNC formula can be left blank or unfilled . If so, the number of digits will be considered as 0 (zero).

Example of the TRUNC Formula

To remove numbers after commas, you can do this by emptying the argument ManyDigits or filling in ManyDigits with the number 0.

If the Number of Digits is positive more than 0 (zero) then the Trunc function will take a decimal number to the right of the number of Numbers of Digits without any rounding.

However, if the Number of digits is negative (less than 0) then the Trunc function will round the number to the right. It acts like the RoundRoundup and Rounddown functions .

For more details, consider the following example of using the TRUNC formula:

Excel ODD Functions/Formulas

The ODD function is an excel function that is used to return a number that is rounded up to the nearest odd integer .

The ODD function can also be referred to as the odd rounding function in excel.

How to Use the ODD Function

How to write or how to use the ODD formula is as follows:



Numbers are filled with numbers that we will round to odd integers.

Example of ODD Formula

To be clear about the meaning of odd rounding, please consider the example of the ODD formula below.

Excel EVEN Functions/Formulas

The EVEN function is the opposite of the ODD function. The EVEN function is an excel function that will return the nearest even integer rounded up.

How to Use the EVEN Function

In the excel formula, how to use the Even function is as follows:



Like the ODD function, the EVEN function also has one argument, which is the number to be rounded to the nearest even number.

EVEN Formula Example

An example of using the EVEN formula in excel is as follows:

In excel formulas, the ODD and EVEN functions always round numbers up. Except for odd integers for ODD and even integers for EVEN which do not contain numeric values ​​after commas/decimals.

Formula to Remove Numbers Behind Commas

After learning some of the excel functions above, how to remove decimal values ​​in excel?

From the series of discussions above, there are several excel formulas that we can use to remove numbers behind a comma or decimal value of a number. Namely with:

  1. Excel INT Rumus formulas
  2. Excel formula TRUNC with blank or zero from argument value Number of digits
  3. Excel formula ODD
  4. Excel formula EVEN
  5. Excel ROUND formula by filling 0 in the number of digits argument
  6. Excel ROUNDUP formula by filling 0 in the number of digits argument
  7. Excel ROUNDDOWN formula by filling 0 in the number of digits argument

Each of the above formulas of course with a note how to round each function.

For clarity, consider the following example:

I hope the explanation above doesn’t make you more confused. If so, don’t hesitate to leave a message in the comments column provided.

Is there a way to do rounding to multiples of a certain number, for example rounding a number to the nearest tens or rounding a number to the nearest hundreds? Of course, there is.

Thus our discussion on how to remove the numbers behind the comma.

If you find the benefits of this tutorial, please SHARE, it so that the benefits can be felt by other friends who need it. Greetings Excel Class.

Latest from Blog