Remove Digits After Decimal in Excel (2 Methods)

Strategic Approaches to Truncating Decimal Digits in Excel

Effectively managing numerical data in a spreadsheet environment often necessitates isolating the whole number component, deliberately discarding the fractional part. In Excel, attempting to solve this purely through cell formatting is insufficient, as formatting only changes how the number is displayed, not the underlying value used in subsequent calculations. To permanently remove and discard digits after the decimal place, specific mathematical functions must be employed.

This article introduces the two highly effective and distinct methods for achieving this truncation: the TRUNC Function and the INT Function. While both yield identical results when dealing with positive numbers, understanding their unique behavior, especially concerning negative values, is crucial for maintaining data accuracy across varied datasets.

Below are the foundational formulas that accomplish the task of extracting the integer component from a floating-point number. Both of these formulas, when applied to a cell containing a numerical value, will effectively remove all fractional digits.

• Method 1: Use TRUNC Function

=TRUNC(B2)

• Method 2: Use INT Function

=INT(B2)

Both of these functional approaches are designed to remove all digits after the decimal place for the number residing in cell B2. The detailed comparison below will explain when to prioritize one over the other for guaranteed precision.

Deep Dive into the TRUNC Function

The TRUNC Function is the most literal method for discarding the fractional part of a number because it performs truncation by simply cutting off the digits at the specified precision level, which defaults to zero. Unlike rounding functions, TRUNC does not consider the value of the discarded digits; it merely chops them off, moving the resulting value closer to zero regardless of the sign of the original number.

The syntax for the TRUNC function is =TRUNC(number, [num_digits]). The number argument refers to the value or cell reference containing the number to be truncated (e.g., B2). The optional [num_digits] argument specifies how many digits to leave after the decimal point. When this argument is omitted, Excel defaults it to 0, ensuring that every digit following the decimal is removed and only the integer remains.

This directional neutrality—always moving the result toward zero—makes TRUNC an excellent choice for straightforward data cleaning where mathematical rounding is explicitly not desired. It is the purest functional representation of simply removing the decimal component.

Detailed Application of the TRUNC Function

To demonstrate the practical use of the TRUNC function, consider the following dataset where we wish to extract the whole number components of the values listed in column B. This dataset provides clear context for our operation.

We initiate the process by typing the TRUNC formula into cell C2. This calculation instructs Excel to take the value from B2 and truncate it to zero decimal places:

=TRUNC(B2)

Following the initial calculation, we leverage Excel’s efficiency features by clicking and dragging the formula down to the remaining cells in column C. This action auto-fills the formula, applying the logic to cells B3, B4, B5, and so on, ensuring quick and accurate conversion across the entire range.

The resulting visual confirmation highlights the successful removal of the fractional digits:

Notice that each cell in column C returns the integer value from the corresponding cell in column B, with the digits after the decimal place successfully and permanently removed. For comprehensive reference on optional arguments, refer to the official documentation for the TRUNC function.

Understanding the Behavior of the INT Function

The INT Function, standing for Integer, achieves decimal removal by rounding a number down to the nearest whole integer. This distinction is subtle but mathematically significant. INT performs a mathematical ‘floor’ operation, meaning it always moves the value toward negative infinity.

For positive numbers, the effect of rounding down is identical to truncation. For instance, 5.2 and 5.9 both become 5 when processed by INT, effectively removing the decimal digits. This is because moving toward negative infinity for a positive number means moving toward the zero end of the number line.

The primary difference arises when processing negative values. If cell B2 contained -5.2, applying =INT(B2) would round the number down to the nearest integer, resulting in -6. This behavior is distinct from the TRUNC function, which would move -5.2 toward zero, resulting in -5. This critical difference dictates which function is appropriate based on the required mathematical outcome when handling mixed positive and negative data.

Detailed Application of the INT Function

Using the same positive dataset, we will now apply the INT function to confirm that for non-negative numbers, the results perfectly match those obtained using TRUNC. This consistency allows users flexibility when their datasets are known to contain only positive values.

We input the following INT formula into cell C2, directing it to calculate the integer floor of the value in B2:

=INT(B2)

Similar to the previous method, we utilize the fill handle to apply the INT function across the entire range in column C, automatically rounding down each number in column B to its nearest integer value.

The resulting worksheet illustrates the outcome of this calculation:

As demonstrated by the output, the calculated values in column C match the previous TRUNC results exactly. This confirms that both functions are equally valid methods for removing the decimal component when the input values are positive. Users are encouraged to select the function they find most intuitive if they are certain their data will not include negative numbers.

For more advanced understanding of its rounding behavior, the complete documentation for the INT function in Excel should be consulted.

Critical Comparison: TRUNC vs. INT for Negative Data

The choice between TRUNC and INT ceases to be arbitrary when the dataset includes negative numbers, as their underlying mathematical definitions lead to diverging results.

The difference can be summarized by their directional goal:

• TRUNC aims for Zero (it removes digits).
• INT aims for Negative Infinity (it rounds down).

If we analyze the input value -10.35, the results are distinct:

1. TRUNC: =TRUNC(-10.35) returns -10. It simply removes the .35 part.
2. INT: =INT(-10.35) returns -11. Since -11 is mathematically smaller (further left on the number line) than -10.35, the function rounds down to the nearest integer below the current value.

This behavior means that if your requirement is strictly to discard the fractional part (i.e., you are performing data cleaning and not a floor operation), TRUNC is the universally safest function, ensuring that the sign and the absolute magnitude of the integer part are preserved, regardless of whether the number is positive or negative. The INT function, while useful for specific mathematical rounding requirements, is not strictly a decimal removal tool for negative numbers.

Alternative Considerations: Formatting vs. Functionality

It is important to briefly address the distinction between using these functions and simply changing the cell format. Formatting options, such as using the ‘Decrease Decimal place‘ button on the Home tab, only affect the visualization of the number. The full precision (e.g., 10.999) remains stored in the cell, and any formula referencing that cell will use the full, unrounded value.

In contrast, both the TRUNC Function and the INT Function modify the number itself, outputting a new, calculated value that is the integer component. If subsequent calculations depend on the value truly having no decimal component, using one of these functions is mandatory.

Therefore, always use TRUNC or INT when the removal of the fractional part must be permanent and functional, ensuring that data integrity is maintained throughout the spreadsheet lifecycle.

Conclusion and Final Recommendation

Both the TRUNC and INT functions provide robust solutions for removing decimal digits in Excel. For datasets containing only positive numbers, the choice between them is a matter of personal preference, as the results are identical.

However, when managing real-world data that frequently includes both positive and negative values, the TRUNC Function offers a mathematically predictable outcome that consistently achieves the goal of eliminating the fractional component by moving the result toward zero. This predictability makes TRUNC the recommended default for general-purpose decimal removal and data truncation tasks.

Always consider the nature of your data and the intended mathematical outcome before deciding. For pure truncation, rely on TRUNC; for floor operations, rely on INT.