TRUNC in Excel

What is the TRUNC Function in Excel?

The Function TRUNC in Excel returns a numeric value. The “number” argument is the input value that is to be truncated, and the “num_digits” argument is the number of digits after the decimal value. It is also known as the Truncate in Excel. The function is categorized under Excel’s mathematical and trigonometrical functions, introduced in 2007.

For example, we apply the TRUNC function in cell B2 to truncate the value of the given number.

TRUNC in Excel - 1

The TRUNC formula is entered in cell B2; the complete formula is =TRUNC(A2), and the result is returned as ‘2.’

TRUNC in Excel - 2

Likewise, using TRUNC in excel function, we can find the output.

Key Takeaways
  • The TRUNC in Excel is also known as the Truncate function in Excel.
  • The syntax of the TRUNC Excel Formula is =TRUNC(number,[num_digits)] where number is the only mandatory argument.
  • The function returns numeric value.
  • If the “num_digits” argument is negative, the value return is zero.
  • The TRUNC and INT functions are similar functions in Excel because they return the integer part of a number. The difference is that the TRUNC function merely truncates a number, but INT rounds off a number.

TRUNC() Excel Formula

The syntax of the TRUNC in Excel Formula is

TRUNC in Excel - Syntax
  • number=This is the mandatory argument. This is the number to be truncated.
  • num_digits=This is the optional argument. This is the number of digits to be truncation. The value given to the “num_digits” argument is as follows:
    • If the value is equal to zero, the function will return the rounded value.
    • If the value is more than zero, indicate the number of digits to be truncated after the decimal.
    • If the value is less than zero, the number of digits must be truncated before the decimal.

How to Use the TRUNC Excel Function?

We can insert TRUNC in excel using the below two methods

1. Access from the Excel ribbon

  • First, choose the empty cell which will contain the result.
  • Next, go to the Formulas tab.
  • Then, select the Math & Trig option from the drop-down menu.
  • Also, select TRUNC from the menu.
  • A window called Function Arguments appears.
  • As the number of arguments, enter the value in the “number” & “num_digits.” 
  • Select OK.

2. Enter the worksheet manually

  • Select an empty cell for the output.
  • Type =TRUNC( in the selected cell. Alternatively, type =T and double-click the TRUNC function from the list of suggestions shown by Excel.
  • Press the Enter key.

Let us take an example to understand this function in detail. The succeeding example depicts the numbers, and now, we will truncate the unwanted digits using the Excel TRUNC function.

In the table, the data is reflected as below: –

  • Column A contains the Value.
  • Column B contains the Number of Digits.
  • Column C contains the Output.
How to Use TRUNC Function in Excel

The steps to evaluate the values by the Function TRUNC in Excel are as follows:

  1. First, select the cell where we will enter the formula and calculate the result. The selected cell, in this case, is cell C2.

  2. Next, we will enter the TRUNC in the Excel formula in cell C2.


    How to Use - Step 2

  3. Then, enter the value of ‘number’ as A2, i.e., the value we need to truncate.


    How to Use - Step 3

  4. Next, enter the value of ‘num_digits’ as B2, i.e., the number of digits we want to truncate.


    How to Use - Step 4

  5. So, the complete formula is =TRUNC(A2,B2) in cell C2.


    How to Use - Step 5

  6. Next, press the “Enter” key. The results in cell C2 are “0.4” in the image below.


    How to Use - Step 6

  7. Press enter and drag the cursor to cell C6, as shown in the following image.


    How to Use - Step 7

    Likewise, using TRUNC in excel function, we can find the output.

Examples

Example #1

The succeeding example depicts the Date and Time as values, and now, we will truncate the Time using the Date TRUNC in the Excel function.

In the table,

  • Column A contains the Date & Time.
  • Column B contains the Number of Digits.
  • Column C contains the Output.
TRUNC in Excel - Example 1

The steps to evaluate the values by the Function TRUNC in Excel are as follows:

  • Step 1: First, select the cell where we will enter the formula and calculate the result. The selected cell, in this case, is cell C2.
  • Step 2: Next, we will enter the TRUNC Formula in the Excel in cell C2.
  • Step 3: Then, enter the value of ‘number’ as A2, i.e., the value we need to truncate. 
  • Step 4: Also, enter the value of ‘num_digits’ as B2, i.e., the number of digits we want to truncate. 
  • Step 5: The complete formula is =TRUNC(A2,B2) in cell C2.
  • Step 6: After entering each value in the preceding step, press the “Enter” key. The results in cell C2 are “02-01-2000” in the image below.
TRUNC Function in Excel - Example 1 - Step 6.gif
  • Step 7: Next, press enter and drag the cursor to cell C6, as shown in the following image.
TRUNC Function in Excel - Example 1 - Step 7

Likewise, we can use TRUNC function to find values.

Example #2

The succeeding example depicts the Date and Time as values, and we will truncate the Date using the Excel TRUNC function.

In the table,

  • Column A contains the Value.
  • Column B contains the Number of Digits.
  • Column C contains the Output.
TRUNC in Excel - Example 2

The steps to evaluate the values by the Function TRUNC in Excel are as follows:

  • Step 1: To begin with, select the cell where we will enter the formula and calculate the result. The selected cell, in this case, is cell C2.
  • Step 2: Next, we will enter the TRUNC Excel formula in cell C2.
  • Step 3: Then, enter the value of ‘number’ as A2, i.e., the value we need to truncate. 
  • Step 4: Also, enter the value of ‘num_digits’ as B2, i.e., the number of digits we want to truncate. 
  • Step 5: So, the complete formula is =TRUNC(A2,B2) in cell C2.
  • Step 6: After entering each value in the preceding step, press the “Enter” key. The results in cell C2 are “05:02:24” in the image below.
Example 2 - Step 6
  • Step 7: Press enter and drag the cursor to cell C6, as shown in the following image.
Example 2 - Step 7

Likewise, using TRUNC function, we can find the output.

Example #3

The succeeding example depicts the Negative numbers, and we will truncate the unwanted digits using the Excel TRUNC function.

In the table,

  • Column A contains the Value.
  • Column B contains the Number of Digits.
  • Column C contains the Output.
TRUNC in Excel - Example 3

The steps to evaluate the values by the Function TRUNC in Excel are as follows:

  • Step 1: First, select the cell where we will enter the formula and calculate the result. The selected cell, in this case, is cell C2.
  • Step 2: Next, we will enter the TRUNC Excel formula in cell C2.
  • Step 3: Then, enter the value of ‘number’ as A2, i.e., the value we need to truncate. 
  • Step 4: Likewise, enter the value of ‘num_digits’ as B2, i.e., the number of digits we want to truncate. 
  • Step 5: So, the complete formula is =TRUNC(A2,B2) in cell C2.
  • Step 6: After entering each value in the preceding step, press the “Enter” key. The results in cell C2 are “-2” in the image below.
Example 3 - Step 6
  • Step 7: Press enter and drag the cursor to cell C6, as shown in the following image.
Example 3 - Step 7

Similarly, we can use TRUNC function to obtain values.

Important Things to Note

  • The TRUNC function is categorized under the Math & Trig functions in the Function Library.
  • The function returns a negative value less than zero, specifying the number of digits to the left of the decimal point.
  • The TRUNC formula returns equal to zero, which specifies the rounded value to the nearest integer.
  • The TRUNC excel function returns a positive value greater than zero, specifying the number of digits to the right of the decimal point.

Frequently Asked Questions (FAQs)

1. What does the TRUNC function do in Excel?

The TRUNC Function in Excel truncates a number to the prescribed number of decimal places. The function can also truncate a number to a given precision. It also extracts dates from date and time values.

The TRUNC function is a built-in function in Excel that is categorized as a Math & Trig Function in Excel. The function was introduced in MS Excel in 2007.

The syntax of the TRUNC function is as follows;
=TRUNC(number,[num_digits])

2. How does the TRUNC function work in Excel?

The TRUNC function works in Excel by following the below steps;
First, select an empty cell for the output.
Next, type =TRUNC( in the selected cell. Alternatively, type =T and double-click the TRUNC function from the list of suggestions shown by Excel.
Then, press the “Enter” key.

For example, we apply the TRUNC function in cell B2 to truncate the value of the given number.

TRUNC in Excel - FAQ 2

The TRUNC formula is entered in cell B2; the complete formula is =TRUNC(A2), and the result is returned as ‘5 & 98.’

FAQ 2 - 1

3. Where is the TRUNC Function in Excel?

We can insert TRUNC in Excel using the following steps:
First, choose the empty cell which will contain the result.
Next, go to the Formulas tab.
Then, select the Math & Trig option from the drop-down menu.
Also, select TRUNC from the menu.

TRUNC in Excel - Inserting from excel ribbon

A window called Function Arguments appears.
As the number of arguments, enter the value in the “number” & “num_digits.” 
Select OK.

TRUNC in Excel - Function argument window

Download Template

This article must help understand the TRUNC Excel Function’s formula and examples. You can download the template here to use it instantly.

This has been a guide to TRUNC in Excel. Here we learn how to use the TRUNC() function formula with step-by-step examples and a downloadable excel template. You can learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published.