There are a variety of ways in Excel to eliminate decimal points and shorten numerical values. In this article, we explain how to use the TRUNC function and what makes it different from other techniques.
What is the TRUNK function?
The TRUNC function truncates a number to a specific number of decimals. The key factor that makes TRUNC different from other functions that eliminate decimals is that the TRUNC function does not round values. If you use TRUNC to remove all decimals from the value 4.68, the result is 4.
The TRUNC function requires two pieces of information:
= TRUNC (número, [dígitos])
The number is the value you want to truncate. Digits are the number of numbers at which you want to truncate the value. The digit portion is optional, and if left unanswered, TRUNC will remove all decimals.
How to use the TRUNK function
Let’s see examples of the TRUNC function with some data examples. The following example uses the following TRUNK function.
= TRONCO (A2)
If you do not specify how many digits you want to cut, all decimals will be removed.
You can see from the value in cell A2 that the TRUNC function does not apply any rounding. Truncate the number to 411.
Let’s look at another example. This time we will reduce the values to two decimal places.
= TRONCO (A2,2)
The TRUNC function will not display additional decimals if you ask it to display more than it has.
Let’s take the following example and cut it to two decimal places.
= TRONCO (A2,2)
The value in cell A4 is reduced to two decimal places, but the values in A2 and A3 remain the same because they already have less than two decimal places.
If you want to display both decimals, the cells will need to be formatted to force them to display.
Remove the time from a timestamp
A useful example of TRUNC is the removal of time from a timestamp in Excel.
Imagine you have the following timestamps, but we only want the date in a single column for your analysis.
The following formula will work to eliminate time.
= TRONCO (A2)
Although the time is removed, the resulting cells will still need to be formatted as the date only.
Use TRUNC to shorten the numbers
This is a rare technique, but it is worth knowing that the TRUNC function will also accept negative numbers for the number argument. When you use a negative number, the formula truncates the numbers to the left of the decimal point. However, the number of digits does not change. It will replace them with zeros.
Let’s look at the following example.
= TRUNC (A2, -1)
You can see in each example that zero was used to replace the number that was subtracted to the left of the decimal point.
There are several ways in Excel to remove decimals, however most will apply roundings of a certain nature. The strong point of the TRUNC function is that it does not round the values and simply shortens them to the specified number of decimals.