Truncate Data in Excel and Google Spreadsheets

In general, to truncate means to shorten an object by cutting it off abruptly - such as truncated branches on a tree.In spreadsheet programs such as Excel and Google Spreadsheets, both number and text data is truncated. The reasons for doing so include:

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

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 no matter what the last digit is.

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 the value of Pi, however, 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 - as shown in row two of the example in the image above.

Truncating Numerical Data in Excel and Google Spreadsheets

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 (short for number of digits). 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  INT 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 Excel and Google Spreadsheets

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.

Truncation Error

A truncation error is an error caused by using a truncated number in calculations. Depending on the number of digits involved, for manual calculations involving might be insignificant.

In the case of computer computations involving data with a large number of decimal places the error can be quite significant.

Rows seven and eight of the example show the results differences when multiplying a truncated and non-truncated number by 100.

Format
mla apa chicago