Truncate Data in Excel and Google Spreadsheets

Shorten digits and other worksheet data

By
Ted French
Ted French
Writer
Former Lifewire writer Ted French is a Microsoft Certified Professional who teaches and writes about spreadsheets and spreadsheet programs.
lifewire's editorial guidelines
Updated on December 8, 2019
3D Rendering, symbol endlessness

Westend61 /  Getty Images

To truncate means to shorten an object by cutting it off abruptly. In spreadsheet programs such as Microsoft Excel and Google Spreadsheets, both number data is truncated with your worksheet using the TRUNC function, while text is truncated using the RIGHT or LEFT function.

These instructions apply to Excel for Microsoft 365, Excel versions 2019, 2016, 2013, and 2010, and Google Spreadsheets.

Rounding vs. Truncation

While both operations involve shortening the length of numbers, the two differ in that rounding can change the value of the last digit based on the normal rules for rounding numbers, while truncation involves no rounding, but simply cutting off data at a specified point.

The reasons for doing so includes:

  • Making it easier to understand data such as reducing the number of decimal places present in a long number.
  • Making items fit such as limiting the length of text data that can be entered into a data field.
Screenshot showing truncating and rounding in Excel

The Formula of Pi

A common example of a number that gets rounded and/or truncated is the mathematical constant Pi. Since Pi is an irrational number; it does not end or repeat when written in decimal form, it continues forever. However, writing out a number that never ends is not practical, so the value of Pi is either truncated or rounded as needed.

Many people answer of 3.14 if asked about the value of Pi. In Excel or Google Spreadsheets, this value can be produced using the TRUNC function.

Truncating Numerical Data

As mentioned, one way of truncating data in Excel and Google Spreadsheets is by using the TRUNC function. Where the number gets truncated is determined by the value of the Num_digits argument.

Screenshot of truncating in Excel

For example, in cell B2 the value of Pi has been truncated to its typical value of 3.14 by setting the value of Num_digits to 3.

Another option for truncating positive numbers to integers is the INT function; it always rounds numbers down to integers, which is the same as truncating numbers to integers as shown in rows three and four of the example.

The advantage of using the INT function is that there is no need to specify the number of digits as the function always removes all decimal values.

Truncating Text Data

In addition to truncating numbers, it is also possible to truncate text data. The decision where to truncate text data depends on the situation. In the case of imported data, only part of the data might be pertinent or, as mentioned above, there may be a limit on the number of characters that can be entered into a field.

As shown in rows five and six of the image above, text data that includes unwanted or garbage characters has been truncated using the LEFT and RIGHT functions. 

Was this page helpful?