Trunc Function in Excel

For example, TRUNC(5.8) will return 5.

Even though the INT function is also used for returning the integer part, it has a drawback. When the INT function in excelINT Function In ExcelINT or integer function in excel returns the nearest integer of a given number and is used when we have many data sets and each data in a different format.read more is used for negative numbers, it rounds the value to a lower number instead of the resulting integer. The present article explains the use of the TRUNC function in Excel with examples and formulas by covering the following topics.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: Trunc in Excel (wallstreetmojo.com)

Explanation

The TRUNC function 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. Moreover, it will be input as a formula into the cells of the Excel sheet.

Syntax

  • 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: The optional parameter specifies 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 function works in all 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.The value is more than zero. It indicates the number of digits to be truncated and presented on the right side of the decimal.The value is less than zero. It shows the number of digits to be truncated and displayed on the left side of the decimal.

How to Use the Truncate Function in Excel? (Example)

Example #1 – Basic Usage of TRUNC Function

This example best illustrates the basic applications of the truncated function.

Only the left part of the decimal results since the default value is zero.

  • In the first step, consider the following data shown in the figure. Place the cursor into the appropriate cell to enter the TRUNC function in Excel. Enter the Excel truncate formula as shown in the figure. Select the cell address of the 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 the “Enter” key to see the result. Apply the formula to the remaining cells by dragging through the mouse. Observe the results shown as mentioned below screenshot.

Example #2 – Applying 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.

Step 1: In the first step, consider the following data shown in the figure.

This example will truncate the number of digits after the decimal point is considered.

Step 2: Place the cursor into the appropriate cell.

Step 3: Enter the TRUNCATE Excel function as shown in the figure.

Step 4: Select the cell address of the number that wants to be truncated and the number of digits.

This cell address is given for values and num_digits parameters, taking the value mentioned in the column. Press the “Enter” key to see the result, as shown below.

Step 5: Apply the formula to the remaining cells by dragging them through the mouse.

Step 6: Observe the results displayed as mentioned below screenshot.

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

Example #3 – To Extract Date from Date and Time

In this example, the number of digits for extracting the date is regarded as zero.

Step 2: Place the cursor into the appropriate “Extracted Date” cell to enter the TRUNC function in Excel.

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

Step 4: Press the “Enter” key to see the result below.

Step 5: Apply the excel formulaExcel FormulaThe term “basic excel formula” refers to the general functions used in Microsoft Excel to do simple calculations such as addition, average, and comparison. SUM, COUNT, COUNTA, COUNTBLANK, AVERAGE, MIN Excel, MAX Excel, LEN Excel, TRIM Excel, IF Excel are the top ten excel formulas and functions.read more to the remaining cells by dragging through the mouse.

As shown in the figure, only the date value is extracted from the date and time using the TRUNCATE function in Excel. If we do not give a number, it takes the default value of zero to the result date.

Application

The TRUNC function has a lot of applications in Excel:

  • For example, this 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 the date.Truncating a decimal number to the nearest integer numberIt is used when there is no need for the grounding of a number.

Things to Remember

  • The 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 a lesser value, but TRUNC (-5.3) returns -5, only removing the fractional value.When num_digits is negative, it returns the truncated value as zero.

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

  • Excel Match Multiple CriteriaMultiple Condition in Excel COUNTIFExcel Convert FunctionMROUND Function in Excel