How to Use TRUNC Function in Excel (4 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

Excel TRUNC function

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.

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



❑ 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 a truncated numeric value.

❑ Version

This function is available from Excel 2000. So any version since Excel 2000 has this function.

How to Use TRUNC Function in Excel: 4 Examples

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,


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.

Excel TRUNC function

➤ 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.

Excel TRUNC function

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 is shown in the image below.


2. Shorten a Number to a Specific Digit with TRUNC Function

Excel TRUNC function can 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,


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.

Excel TRUNC function

➤ 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 carefully observe cells 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.

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,


The formula will truncate the time portion from the date and time of cell B5.

Excel TRUNC function

➤ 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.

Excel TRUNC function

Read More: How to Truncate Date in Excel

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.

VBA Window

➤ Close the VBA window.

Now, you will see the numbers with two decimal points in column C.


Read More: How to Use Truncate in Excel VBA

💡 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. However the TRUNC function is easier to use because it requires fewer arguments.

📂 Download Practice Workbook


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.

TRUNC Function in Excel: Knowledge Hub

<< Go Back to Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Prantick Bala
Prantick Bala

PRANTICK BALA is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, he has transitioned into the role of a content developer. Beyond his work, find him immersed in books, exploring new... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF