WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Trunc in Excel

Trunc in Excel

By Madhuri ThakurMadhuri Thakur | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Trunc Function in Excel

Trunc is the excel function used 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 the INT function in excel 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.

Truncate Function in Excel

Explanation

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. It will be input as a formula into the cells of the Excel sheet.

Syntax

TRUNC formula 1

  • 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 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
  • 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 indicates the number of digits to be truncated and presented on the left side of the decimal

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

You can download this Truncate Function Excel Template here – Truncate Function Excel Template

Example #1 – Basic Usage of TRUNC Function

This example best illustrates the basic applications of the truncated function

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

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

TRUNCATE Excel Function - Example 1

Step 3: Enter the excel truncate formula as shown in the figure

TRUNCATE Excel Function - Example 1-1

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

TRUNCATE Excel Function - Example 1-2

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.

TRUNCATE Excel Function - Example 1-3

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

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Step 6: Observe the results shown as mentioned below screenshot

TRUNCATE Excel Function - Example 1-4

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

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

TRUNCATE Excel Function - Example 2

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

Step 2: Place the cursor into the appropriate cell.

TRUNCATE Excel Function - Example 2-1

Step 3: Enter the truncated excel function as shown in the figure

TRUNCATE Excel Function - Example 2-2

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

TRUNCATE Excel Function - Example 2-3

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

TRUNCATE Excel Function - Example 2-4

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

Step 6: Observe the results shown as mentioned below screenshot

TRUNCATE Excel Function - Example 2-5

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 – To Extract Date from Date and Time

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

Example 3

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

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

Example 3-1

Step 3: Enter the truncated excel function as shown in the figure

Example 3-2

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

Example 3-3

Step 4: Press Enter to see the result as shown below

Example 3-4

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

Step 6: Observe the results shown as mentioned below screenshot

Example 3-5

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.

Application

The Trunc function has a lot of applications in Excel

  • 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 date
  • Truncating a decimal number to the nearest integer number
  • It is used when there is no need for grounding of a number

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 a 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 using the TRUNC formula along with examples and a downloadable excel template. You may learn more about excel from the following articles –

  • Excel Match Multiple Criteria
  • Multiple Condition in Excel COUNTIF
  • Excel Convert Function
  • MROUND Function in Excel
5 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Truncate Function Excel Template

Special Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More