Excel Trunc (Table of Contents)
Definition of Trunc in Excel
Trunc is also known as Truncate function in excel. It is considered as one of the mathematical and trigonometrical functions of the excel. The output returned by this function is a numeric value. The syntax for this simple function is =TRUNC( number, number of digits). Out of these two parameters, the first argument is a mandatory argument and the second argument is an optional argument. Let us discuss what these two arguments are. The first argument is the input we provide to the function which is the number to be truncated and the second argument is the number of digits we want to be displayed in a decimal value input.
How to Use Trunc in Excel?
Trunc function has two arguments where one is mandatory and one is optional. The mandatory argument is actually the integer input. Trunc function is similar to the INT function in excel because both functions give us the result for the integer part of the number.
Example #1
Let us go through our basic example to learn how we use the Trunc function or also known as truncate function in excel. Let assume we have a value of 2.1 in cell A1 as shown below:
We have value in cell A2, let us use the function in cell B2 as follows:
We know that the first argument is mandatory, which is the number we want to truncate so select cell A2 for the following argument:
The second argument is optional which is given when we need to remove a part of the number which can be a fractional part of the decimal part so we can close the parenthesis.
When we press the Enter key we will have our final result as shown below:
Example #2
For this example, we have a bunch of numbers which are in decimal parts and they are positive integers, we will use a truncate function for these numbers as shown below:
Let us use the number of digits to be truncated is 2.
In cell B2 type the following formula as shown in the below screenshot.
Select the number from cell A2 as I have shown in the below screenshot.
Now after the number put a comma and type 2 for the number of digits to be truncated as shown below:
Press Enter Key and see the result.
Copy the formula and drag it down to the last cell as shown below.
The values in cell B2 are the truncated numbers which are truncated to the two digits from the original number.
Example #3
In the previous example, we used positive integers to show how the trunc function works. Now let us see we will get the same result for the negative integers. We have the following number in cell A2 as shown below.
Again let us use the number of digits to be truncated is 2. In cell, B2 type the following formula as shown in the below screenshot.
Select the number from cell A2 as I have shown in the below screenshot.
Now after the number put a comma and type 2 for the number of digits to be truncated as shown below:
Press Enter Key and see the result.
Copy the formula and drag it down to the last cell as shown below.
In column B we have the desired result from the Truncate function.
Example #4
Now there is one example when we get data from some external source and the date from the source has timestamp attached to it. So can we use the Trunc function to extract the date from the timestamp? Let us find out. I have below dates in the timestamp format.
In cell C1 type the following formula as shown in the screenshot below:
For the number argument select cell A1.
For the number of digits to be truncated select cell B1, since it is blank it will be considered as 0.
Press Enter Key and see the result.
Now copy and drag the formula to cell C7 as shown in the screenshot below.
We have successfully extracted the date using the Trunc function from the data.
Things to Remember
- Trunc function is one of the mathematical and trigonometrical functions of excel.
- INT and TRUNC functions are similar.
- The values returned by this function are numeric.
- Trunc function is categorized under the mathematical and trigonometrical functions of excel. This function gives us a numerical value. This function is used to return a number to a specified number of digits.
Recommended Articles
This is a guide to Trunc Function in Excel. You can also go through our other related articles –
