Shorten digits and other worksheet data
Westend61 / Getty Images
- Tweet
- Share
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.
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.
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.
Do you have text data in Excel that you want to truncate ? Well, in most cases, Excel users deal with text that needs to be shortened in order to be displayed the easy it should be. For you to do this, you have to learn how to use the trunc function. In this post, we shall look at easy steps to truncate a cell .
Step 1: Prepare your data sheet
The first thing that you need to do in order to truncate characters is to have the data in a worksheet. If you have a spreadsheet with the data, you can simply double-click on it to open. But if you do not have it, then you will have to enter it into a sheet.
If you want to truncate characters for a whole column, you need to ensure that you have all the text string to truncate in one column.
Step 2: Select cell/column where you want the truncated text string to appear
The next thing is to have a cell where you will have the truncated text string. Note that if you are doing this for a whole column, then you will also have to prepare a whole column to display the truncated text .
Figure 1: Truncating text
Step 3: Type the RIGHT or LEFT truncating formula in the target cell
The next thing we need to do is to type the truncation formula in the cell where we want our first result to appear. Note that unlike how we truncate numbers, we use the LEFT or RIGHT formula to truncate text strings.
We have the formula below in cell C2;
=LEFT (B6783,7)
- B6783 refers to the target cell to truncate and 7 refers to the number of characters we want to remain with after truncation. After truncating, we shall have the first 7 characters displayed.
We can also use the RIGHT function to truncate text . When we use the RIGHT function, we shall only have the last characters as specified in the formula displayed.
For example, if we had the following formula in the above example, we shall have the last 7 characters displayed.
=RIGHT (B6783, 7)
Instant Connection to an Expert through our Excelchat Service
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
