Trunc in Excel

Trunc Function in Excel

Excel has a feature of extracting the integer data from the decimal data. It uses a few predefined functions for this task. Among them, Trunc is the excel function to trim the decimal numbers to integers. It is one of the trigonometry and mathematic functions introduced in the 2007 version of excel. TRUNC returns only the integer part by eliminating the fractional portion of a decimal number by specifying the required precision. Even though INT is also used for returning the integer part, there is a drawback with it. When INT function is used for negative numbers, it rounds of value to a lower number instead of the resulting integer part. The present article explains the use of truncate function in excel with examples and formula by covering the following topics.

Explanation of Trunc (Truncate) Function in Excel

Description of Truncated Function: Trunc function in excel returns the trimmed value of a number based on the number of digits. It is a built-in function utilized as an excel worksheet function. It will be input as a formula into the cells of the Excel sheet.

Syntax: The syntax to use the excel truncated formula is

• Parameters Required: The parameters used by the TRUNC function are identified as number and num_digits
• Number: It is the mandatory parameter that a user wants to truncate the digits of the fractional part
• num_digits: It is the optional parameter used to specify the number of digits to be truncated after decimal numbers. The default value of this parameter is 0 if no value is given
• Returns: This function results in a numeric number omitting the fractional part

This Trunc excel function works in all kinds of excel versions including 2007, 2010, 2013, 2016, and office 365.

If the value is given to the parameter of Num_digits

• Value is equal to zero, the function returns the rounded value
• Value is more than zero, it indicates the number of digits to be truncated and presented the right side of the decimal
• Value is less than zero, it indicates the number of digits to be truncated and presented a left side of the decimal

How to Use the Truncate Function in Excel?

The Trunc function has a lot of applications in Excel

• The truncate excel function is useful in financial analysis to trim the values to preferred precision levels.
• It is used in mining the time and date values from date
• Truncating a decimal number to nearest integer number
• The TRUNC excel function used when there is no need for grounding of a number

Examples of Trunc Function in Excel

Below are the examples of truncate function in excel.

Example #1 – Understanding the Basic Usage of Excel TRUNC Function

This example best illustrates the basic applications of the truncated function

Step1: In the first step, consider the following data shown in the figure

Step2: Place the cursor into the appropriate cell to enter the TRUNC function in excel

Step3: Enter the excel truncate formula as shown in the figure

Step4: Select the cell address of number that wants to be truncated

In this, no cell address is given for the num_digits parameter and it takes the default value of zero. Press Enter to see the result.

Step5: Apply the formula to the remaining cells by dragging through the mouse.

Step6: Observe the results shown as mentioned below screenshot

In this, only the left part of the decimal results since the default value is zero

Example #2 – Applying Excel TRUNC Function on a Set of Decimal Numbers

This example best illustrates the basic applications of the truncated function on the set of decimal numbers

Step1: In the first step, consider the following data shown in the figure

In this example, the number of digits after decimal point going to be truncated are considered

Step2: Place the cursor into the appropriate cell to enter the TRUNC function in excel

Step3: Enter the truncated excel function as shown in the figure

Step4: Select the cell address of number that wants to be truncated and number of digits

In this, cell address is given for values and num_digits parameters and it takes the value mentioned in the column. Press Enter to see the result as shown below

Step5: Apply the formula to the remaining cells by dragging through mouse

Step6: Observe the results shown as mentioned below screenshot

In this, the right part of a decimal number is resulted based on the value given in the column of a number of digits. A number of digits 5 indicates a decimal number is truncated to 5 digits after the decimal point.

Example #3 – Applying the Excel TRUNC Function in Extracting the Date from Date and Time

This example best illustrates the use of truncated excel function in extracting date

Step1: In the first step, consider the following data shown in the figure

In this example, the number of digits for extracting date are considered as zero

Step2: Place the cursor into the appropriate cell called “Extracted Date” to enter the TRUNC function in excel

Step3: Enter the truncated excel function as shown in the figure

In this, cell address is given for date and time and num_digits parameter as zero as mentioned in the column.

Step4: Press Enter to see the result as shown below

Step5: Apply the excel formula to the remaining cells by dragging through mouse

Step6: Observe the results shown as mentioned below screenshot

As shown in the figure, only the date value is extracted from date and time using the truncated function excel. If we don’t give a number of digits, it takes the default value of zero to the result date.

Things to Remember

• INT and TRUNCATE excel functions are identical since they result in integer values. But, these are different when they are used with negative, For example, INT(-5.3) returns -6 since it is lesser value but TRUNC (-5.3) returns -5 only removing the fractional value
• When the value of Num_digits is negative, it returns the truncated value as zero

Recommended Articles

This has been a guide to Trunc Function in Excel. Here we learn how to how to use the truncate function in excel using TRUNC formula along with examples and downloadable excel template. You may learn more about excel from the following articles –

• How to Match Multiple Criteria in Excel?
• Multiple Condition in Excel COUNTIF
• Examples of Index Match Multiple Criteria
• Example to Compare Excel Columns for Matches
• Scientific Notation Excel
• MROUND Function in Excel
• Excel as Calculator
• Formula of Excel Minus