Trim data in Excel and Google Sheets

To crop means to trim an object by cutting it sharply. Spreadsheet programs such as Microsoft Excel and Google Sheets both truncate numerical data with your spreadsheet using the function TRUNC and the text is truncated with TURN RIGHT or LEFT. function. Reasons for this include:

  • Make data easier to understand, such as reducing the number of decimal places in a long number.

  • Appropriate elements, such as limiting the length of text data that can be entered into a data field.

Remark . These instructions apply to Excel 2019, 2016, 2013, 2010, Excel for Office 365, and Google Sheets.

  1. Rounding vs truncation
  2. Pi formula
  3. Truncating Numeric Data
  4. Shorten text data

Rounding vs truncation

While both operations shorten the length of numbers, they differ in that rounding can change the value of the last digit based on the normal rules of rounding numbers, whereas truncation does not involve rounding but simply truncates the value to a certain point.

Pi formula

A common example of a number being rounded and/or truncated is the mathematical constant Pi. Because Pi is irrational number ; it doesn’t end or repeat when written in decimal, it goes on forever. However, it is impractical to write a number that never ends, so Pi is truncated or rounded as necessary.

Many, when asked about the value of pi, give the answer 3.14 – the answer they learned in a math class. In Excel or Google Sheets, this value can be obtained using the TRUNC function.

Truncating Numeric Data

As mentioned, one way to truncate data in Excel and Google Sheets is to use the function TRUNC . Where the number is truncated is determined by the value of the argument Number_digits .

For example in a cell B2 the value of Pi has been truncated to the typical value of 3.14 by setting the value Number_digits Directly 3 .

Another option for truncating positive numbers to integers is the function INT ; it always rounds numbers to integers, which is the same as truncating numbers to integers, as shown in lines 3 and 4 of the example.

The advantage of using a function INT is that it is not necessary to specify the number of digits, because the function always removes all decimal values.

Shorten text data

In addition to truncation of numbers, it is also possible to truncate text data. The decision to truncate text data depends on the situation. In the case of imported data, only some of the data may be relevant or, as mentioned above, there may be a limit to the number of characters that can be entered in a field.

As shown in the fifth and sixth lines above, text data containing unwanted or unnecessary characters were trimmed using LEFT and TURN RIGHT functions.

Leave a Reply

Your email address will not be published. Required fields are marked *