Prantick Bala
The TRUNC function in Excel truncates a number to a specified number of digits. It is under the category of Excel Math and Trigonometry function. The function is mainly used for removing the decimal parts from a number.
From the above image we can get a general overview of the TRUNC function. Throughout the article we will see the details of this function.
📂 Download Practice Workbook
Introduction to the TRUNC Function
❑ Objective
Excel TRUNC function truncates a number to an integer by removing the decimal, or fractional, part of the number.
❑ Syntax
TRUNC(number,[num_digits])
❑ Argument Explanation
| Argument | Required/Optional | Explanation |
|---|---|---|
| number | Required | A number which will be truncated |
| num_digits | Optional | The number of decimal places to return in the truncated number.If this argument is omitted, there will be no decimal part in the returned number. |
❑ Output
The TRUNC function returns truncated numeric value.
❑ Version
This function is available from Excel 2000. So any version since Excel 2000 has this function.
4 Examples of Using TRUNC Function in Excel
Now, we will see different examples where various applications of the TRUNC function are shown.
1. Remove Decimal Parts of a Number
We can remove decimal parts of a number by using the TRUNC function. Suppose we have a dataset where we have some numbers with decimal points. Now, we will apply the TRUNC function to remove the decimal parts of the numbers.
➤ Type the following formula in cell C5,
=TRUNC(B5)
The formula will truncate the number of the cell B5 in such a way that there will be no decimal parts in the returned number.
➤ After that, press ENTER.
As a result you will get the integer part of the number of cell B5 in cell C5.
➤ Now drag the cell C5 to the end of your dataset to apply the same formula for all of the numbers.
If you observe you can see that the TRUNC function returns zero for any number between 0 to 1.
We can use other functions such as Excel INT function, Excel ROUND function or Excel ROUNDDOWN function instead of the TRUNC function to remove decimal parts of a number. The application of these functions for this example are shown in the image below.
Read More: 51 Mostly Used Math and Trig Functions in Excel
2. Shorten a Number to a Specific Digit with TRUNC Function
Excel TRUNC function can be used to shorten a number to a specific digit. Let’s say, we have some numbers in our dataset in column B and the number of digits we want after the decimal point are given in column C. Now, we will use the TRUNC function to shorten these numbers to specified digits.
➤ First, type the formula in cell D5,
=TRUNC(B5,C5)The formula will shorten the number of cell B5 to the specified digit of cell C5 and will return the shortened number in cell D5.
➤ Press ENTER.
And we will get the shortened number in cell D5.
➤ At last, drag the cell D5 to the end of your dataset.
As a result, we will get all the numbers shortened to the specific digits we have mentioned in column C.
If you observe carefully you can see cell C8 and C9 have negative numbers as the digits. The formula eliminates digits from the integer part of the number and returns 0 at that place when the specified digit is negative.
Read More: 44 Mathematical Functions in Excel (Download Free PDF)
Similar Readings
- How to Use SIN Function in Excel (6 Easy Examples)
- How to Use Excel PI Function (7 Examples)
- Use Excel QUOTIENT Function (4 Suitable Examples)
- How to Use MMULT Function in Excel (6 Examples)
- VBA EXP Function in Excel (5 Examples)
3. TRUNC Function to Remove Time from Date and Time Cells
TRUNC function can also be used to remove time from date and time cells. Suppose at this time we have some dates and times in our dataset. We want to remove the time part and extract only the date part from these dates and times.
➤ First type the following formula in cell C5,
=TRUNC(B5)The formula will truncate the time portion from the date and time of cell B5.
➤ After that, press ENTER
As a result, you will see the time portion is showing 0:00 in cell C5.
➤ Drag the cell C5 to the end of your dataset to apply the same formula for all other dates and times.
We can remove the 0:00 from these cells. To do that,
➤ Go to Home > Number and select Short Date.
As a result you will see 0:00 is removed from the cells. Now, we have only the dates.
4. TRUNC Function in VBA
TRUNC is not a part of the application.worksheetfunction. As a result, it can’t be utilized in Excel VBA. But, we can apply the FORMAT function to achieve the same result. Suppose we have the following dataset where we want to convert the number with two decimal points.
To do that first,
➤ Press ALT+F11 to open the VBA window and press CTRL+G to open the Immediate box in the VBA window.
After that,
➤ Insert the following code in the Immediate box line by line and press ENTER after every line.
Range("C5") = Format(Range("B5"), "##.##")
Range("C6") = Format(Range("B6"), "##.##")
Range("C7") = Format(Range("B7"), "##.##")
Range("C8") = Format(Range("B8"), "##.##")
Range("C9") = Format(Range("B9"), "##.##")
Range("C10") = Format(Range("B10"), "##.##")The code will return the numbers of column C with two decimal points in column D.
➤ Close the VBA window.
Now, you will see the numbers with two decimal points in column C.
💡 Things to Remember When Using TRUNC Function
📌 The TRUNC function will give #VALUE! error, if you give input in text format.
📌 The INT function and the ROUNDDOWN function give the same result as the TRUNC function. But the TRUNC function is easier to use because it requires fewer arguments.
Conclusion
I hope now you know how the Excel TRUNC function works and how to apply the function in different conditions. If you have any confusion please feel free to leave a comment.
