How to Truncate a Number in Excel?

To truncate a number in Excel, you use the TRUNC function.

The TRUNC function removes the fractional part of a number, effectively truncating it to a specified number of decimal places. Unlike rounding functions, TRUNC simply cuts off the extra digits without rounding up or down.

Understanding the TRUNC Function

The core syntax for the TRUNC function is derived from the provided reference:

=TRUNC(number, [num_digits])

Let's break down the arguments:

• Number (Required Argument): This is the number you wish to truncate. This can be a direct number, a cell reference (e.g., A1), or a formula that results in a number.
• Num_digits (Optional Argument): This argument specifies the precision of the truncation. It determines how many digits you want to keep after the decimal point.
  • If Num_digits is positive, it truncates to that many digits to the right of the decimal point.
  • If Num_digits is 0 or omitted (left blank), the function truncates the number to an integer (removes all decimal places). According to the reference, If kept blank, it will take 0 as the default value.
  • If Num_digits is negative, it truncates the number to the left of the decimal point (e.g., -1 truncates to the nearest ten, -2 to the nearest hundred).

Practical Examples

Here are some examples showing how to use the TRUNC function:

• Truncating to an Integer (Removing Decimals):

  • To truncate 123.456 to 123:
    =TRUNC(123.456)
    (Since num_digits is omitted, it defaults to 0, keeping no decimal places)
  • Alternatively:
    =TRUNC(123.456, 0)

• Truncating to a Specific Number of Decimal Places:

  • To truncate 123.456 to 2 decimal places (123.45):
    =TRUNC(123.456, 2)
  • To truncate 987.654321 to 4 decimal places (987.6543):
    =TRUNC(987.654321, 4)

• Truncating using a Cell Reference:

  • If cell A1 contains the number 789.12345:
    • To truncate A1 to an integer: =TRUNC(A1)
    • To truncate A1 to 3 decimal places: =TRUNC(A1, 3)

Summary Table

Function Syntax	Description	Example Result
=TRUNC(Number)	Truncates the number to an integer (removes all decimal places). Num_digits defaults to 0.	TRUNC(5.876) gives 5
=TRUNC(Number, 0)	Explicitly truncates the number to an integer.	TRUNC(5.876, 0) gives 5
=TRUNC(Number, Positive_Digits)	Truncates the number, keeping the specified number of digits after the decimal point.	TRUNC(5.876, 1) gives 5.8
=TRUNC(Number, Negative_Digits)	Truncates the number to the left of the decimal point (e.g., -1 truncates to the nearest 10, -2 to the nearest 100).	TRUNC(12345, -2) gives 12300

By using the TRUNC function with the appropriate num_digits argument, you can easily control how many decimal places are kept or removed from a number in your Excel spreadsheets.