Truncate Data in Excel and Google Spreadsheets

3D Rendering, symbol endlessness
Don't let your data go on forever, truncate it.

 Getty Images/Westend61

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. 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.

Note: These instructions apply to Excel versions 2019, 2016, 2013, 2010, Excel for Office 365, 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 a value at a specified point.

Screenshot showing truncating and rounding in Excel

The Formula of Pi

A very 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 terminate or repeat when written in decimal form, it continues on 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, if asked about the value of Pi, give the answer of 3.14 — the one the learned in math class. 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 a portion 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.