When working with numerical datasets, financial ledgers, or measurement calculations in Google Sheets, standard spreadsheet formatting often alters visual precision by rounding values up or down. If a cell contains 14.99 and you apply standard zero-decimal formatting or standard rounding functions like ROUND, Google Sheets automatically converts the figure to 15. However, in inventory counts, currency truncation, fractional unit accounting, or timestamp stripping, you frequently need to drop decimal figures cleanly without performing any upward or downward rounding adjustments.
This is where the TRUNC (truncate) function comes in. In this guide, you will learn the exact syntax, configuration parameters, and real-world formula combinations for using the TRUNC function in Google Sheets.
Understanding the Syntax of TRUNC
The TRUNC function cuts off numbers to a specified number of fractional digits without altering the remaining preceding integers or digits. Its formula syntax is straightforward:
=TRUNC(value, [places])Argument Breakdown
- value (Required): The numerical value, cell reference, or calculation formula you want to truncate.
- places (Optional, default =
0): An integer defining how many decimal places to retain. If omitted, TRUNC removes all decimal digits and returns only the integer portion of the number.
TRUNC vs ROUND vs INT: Key Differences
Understanding when to employ TRUNC instead of alternative mathematical functions prevents data inaccuracies:
| Function Call | Input Number | Result | Underlying Mathematical Behaviour |
|---|---|---|---|
=TRUNC(8.79) |
8.79 | 8 | Discards all decimal digits; zero rounding performed. |
=ROUND(8.79) |
8.79 | 9 | Rounds mathematically to nearest integer (upward because .79 >= .5). |
=INT(-4.25) |
-4.25 | -5 | Rounds downward to the next lowest integer on the number line. |
=TRUNC(-4.25) |
-4.25 | -4 | Trunk cuts toward zero, preserving the whole number magnitude. |
The difference between TRUNC and INT with negative values is particularly vital for financial analysts. INT(-4.25) evaluates to -5 because it rounds down towards negative infinity, whereas TRUNC(-4.25) truncates towards zero to yield -4.
Practical Examples of Using the TRUNC Function
1. Stripping Decimals to Retain Whole Numbers Only
To discard everything past the decimal point, omit the optional second parameter:
=TRUNC(A2)If cell A2 holds 128.847, the formula returns 128.
2. Truncating to Specific Decimal Digits
When tracking pricing tiers or interest rates where fractional pennies must not be artificially rounded up, set places to 2:
=TRUNC(A2, 2)If cell A2 contains 23.498, standard rounding would produce 23.50. TRUNC(A2, 2) cleanly yields 23.49.
3. Truncating to Tens, Hundreds, or Thousands (Negative Places)
Similar to the ROUND function, TRUNC accepts negative numbers for the places argument to truncate digits to the left of the decimal point:
=TRUNC(54829, -2)This truncates the tens and units places to zero, returning 54800.
4. Extracting Only the Decimal Remainder
To isolate the fractional part of a number without its whole integer, subtract the truncated value from the original number:
=A2 - TRUNC(A2)If A2 contains 45.72, this formula returns 0.72. This is exceptionally useful for isolating hours and minutes from decimal timestamp quantities.
Combining TRUNC with ARRAYFORMULA for Entire Columns
Rather than dragging down formulas manually across thousands of rows, pair TRUNC with ARRAYFORMULA to process complete datasets dynamically:
=ARRAYFORMULA(IF(ISBLANK(A2:A), "", TRUNC(A2:A, 1)))This formula checks column A starting from row 2, automatically truncating every numeric entry to one decimal place while leaving empty cells blank.
Troubleshooting Common Errors with TRUNC
- #VALUE! Error: Occurs when the target cell contains text or non-numeric characters. Use
=TRUNC(VALUE(A2), 2)or verify that numbers are not stored as plain text strings with leading apostrophes or non-breaking spaces. - Negative Arguments in Unexpected Places: Supplying a negative number for
placesmodifies whole digits, not decimal places. Ensureplacesis positive when preserving fractional amounts. - Visual Formatting Confusion: If you apply the “Decrease decimal places” button in the Google Sheets toolbar, the spreadsheet only masks digits visually while keeping underlying decimals active in formulas. TRUNC permanently alters the computed mathematical value passed into downstream calculations.
Mastering the TRUNC function gives you full control over numerical hygiene, financial precision, and dataset transformation in Google Sheets without introducing unwanted rounding bias.