How to Truncate Numbers and Text in Excel (2 Methods)

Excel can be a powerful tool for analyzing data. When you have a long list of values that you need to analyze, it can be beneficial to shorten that list by using the function “truncate”. Truncating data in Excel can be a helpful way to reduce the amount of data you need to review and can help you to make faster decisions based on the data you have. In this blog post, we will discuss how to use the “truncate” function in Excel and how it can be used to simplify and condense large datasets. We’ll look at how to truncate data based on a character limit, allowing you to only view the most pertinent information without having to scroll through a large dataset. We’ll also discuss the importance of making sure your data is formatted correctly before applying the truncate function to make sure you don’t lose information or misinterpret your results. So join us as we explore the power of

Excel TRUNC Function Tutorial

How is truncating used?

Here are some situations in Excel where you might want to truncate a number:

To create a whole number

To convert a number from a fraction to a whole number known as an integer, financial analysts can truncate the number in Excel. It may be simpler to use integers in formulas than decimals, especially if your formula contains function-limiting characters. For these functions, decimals may produce too much data, which could lead to mistakes. You can truncate a number to get a less accurate but more useful result.

To simplify data into a standard

You can truncate numbers in order to create data standards if you’re creating a chart or want to sort your spreadsheet by data. For instance, you might want to sort each entry by month in a series of entries with dates and months throughout the year. You can truncate to create categories based on the month. Truncating data can also assist in the creation of a chart or graph that needs standardized entries. You can edit entries to make the data simpler by truncating text strings and integers.

To determine a date

To help you separate information, Excel lets you truncate dates and times. Dates and times are formatted in many programs or records in the same cell. The spreadsheet’s formatting can be standardized by using the same number of digits for dates and times. If you only want to sort by date, you can truncate the cell to remove the time-related digits.

You may be able to more clearly organize your data by using this function. For instance, if your cell reads “11/29/21 13:30,” you can remove the first five characters to get the entry date “11/29/21.”

What is truncating in Excel?

Data can be made simpler by truncating in Excel, also known as the TRUNC function. After a certain point in the string of integers, it enables you to make an estimate of a number without knowing the precise digits. Instead of rounding off numbers, it displays them with the appropriate number of decimal places. To indicate the size of the number without being precise, the erroneous integers are frequently replaced with zeros. To achieve a standard accuracy level for your data, you can choose in Excel how many digits to truncate.

Because text strings don’t contain decimals, you can truncate text using a variety of functions in Excel. You select the number of characters you want to see in the text string.

How to truncate numbers in Excel

You can follow these steps to truncate numbers in Excel:

1. Prepare the data

Having all of your data in an Excel worksheet that displays all of the decimals is the first step. To do this, choose the data column you want to truncate, then click the “Home” tab in the program’s toolbar. To access formatting options for numbers in your spreadsheet, click the “Number” icon. Decimal points with arrows indicating whether the zeros are increasing or decreasing are displayed in two icons. When all non-zero decimal points are displayed, select the icon with the left-pointing arrow. Having access to these decimals makes it easier to avoid rounding your data.

You can substitute numbers for text or fractions in the worksheet cells if they are not already formatted. Selecting the “Number” Icon and choosing “Number” from the drop-down menu that appears will enable you to do this.

2. Decide the number of digits you want

After formatting the data, you can get the spreadsheet’s remaining rows ready for trimming. Determine how many blank lines you want to follow the decimal point in your spreadsheet. You can enter a standard number of decimal points into the function you’re using if you have them.

You can also truncate to different decimal points. You can create a column to the right of the data you want to truncate in order to have distinct digits for each row. Label this column “Digits. You can specify the number of digits to display after the decimal point for each individual row in this column. For example, if you have three numbers, 12. 1223, 14. 664, and 2. If you have the number 3443 and want to truncate the digits to a different decimal point for each number, you can do this by creating two columns that show the original number and the digits you want to retain:

3. Create the results column

You must add a column to your spreadsheet in order to insert the TRUNC function and fill it with the result before using it. Choose your Digits column or a column to the right of the data. It may be simpler to sort, search, or hide the original data if the data is positioned to the right, which will also make your spreadsheet look cleaner.

If there is an empty column next to that data, label it and move on to the next step. If you don’t have an empty column, you can insert a column to the left of the column you selected by right-clicking the column to the right of your data and choosing “Insert” from the drop-down menu. Then, label the column. Keep in mind that adding a column can alter the cell references in already-written formulas.

4. Insert the TRUNC formula.

Put an “=” sign to indicate you’re using a formula in the first cell of the column you want to fill with the truncated number. Then type TRUNC. To add the formula’s operators, place them inside an open parenthesis. The number you want to truncate is the first one you enter. You might want to check your formula again because typos can cause problems that make it difficult to copy the formula or change the starting value.

You can add a comma to separate the two operations and then the number of decimals you want to display if you don’t want to display the original number anywhere on the spreadsheet. If all of the cells display the same number, you can accomplish this by typing it in. For example, your formula may look like this:

=TRUNC(13.565656,2)

And return the number 13. 56, with the four extra digits changed to zeros and removed in accordance with Excel’s built-in number formatting.

5. Add cell references

Consider adding a cell reference by choosing the cell that contains the data you want to truncate in place of typing the number. Using this technique, you can use the same formula to fill in the remaining cells in the column. The formula is dynamic, which means that if the data changes, it also changes. If you’ve used a digits column to indicate different digits for each row, choose the corresponding cell to create a cell reference. Then close parenthesis and hit “Enter. ” The formula may look like this:

=TRUNC(A2, B2)

Cell A2 contains 13. B2 contains the number two to denote the number of digits following the decimal, which is 565656. This formula returns the number 13. 56, where the extra four digits have been changed to zeros and are no longer present.

6. Copy the formula down the column

Selecting the tiny green square on the cell’s bottom right side will allow you to fill in the remaining cells in the column. To add the formula to the remaining cells in the column, double-click the square. Double-check the data each time you fill a cell. In the case of cell references, they reflect the identical cells in the rows that correspond to the row where the formula was pasted. If you entered any numbers, you might copy them to the other cells and the outcome wouldn’t reflect the information in its row.

How to truncate text in Excel

Because text strings don’t contain decimals, you can truncate text using a variety of functions in Excel. You can use these steps to simplify or organize your data with the RIGHT and LEFT functions:

1. Prepare the data

Text may contain a variety of factors. The data can be made ready by being formatted into a readable text or size. Additionally, you can use the trim function to make sure there aren’t any extra spaces that could cause your truncation result to return the incorrect number of characters. Create a new column to the right of the current data in order to use the trim formula, keeping in mind that inserting a column can change formulas that use cell references. In the new column, type the following formula:

=TRIM(Cell reference), where cell reference denotes the cell containing the desired data to be trimmed.

2. Determine the number of characters you want to return

You can select the number of characters you want to appear in the result by using the TRUNC formula. Choose one number and enter it in the appropriate spot in the formula if you want the same number of characters to appear for each row. When truncating for numbers, you can create a digits column to hold the unique digits you want to return if each row has a different answer. You can then use a cell reference in the formula to produce unique returns.

3. Decide between the RIGHT or LEFT function

You can also specify whether you want the formula to return characters that start on the right or left side of the cell when you truncate text by using either the RIGHT or LEFT function. The RIGHT function, for instance, can be used to return only the first three letters of each last name in a list of last names. You can use the LEFT function to specify that you want the result to display the last two characters in the cell if you have a list of cities with the state abbreviation listed after it.

4. Enter your formula

Each function begins with an equal sign to show that it is a formula. After the equal sign, type RIGHT or LEFT and then an open parenthesis, depending on your requirements. The text string you want to truncate can be indicated by a cell reference as the first operator following the parenthesis. To start a new entry, type a comma, followed by the number of characters you want to return or a cell reference for the same data. To fill the cell with the outcome, close the parenthesis and press “Enter.”

If you used the LEFT function, for instance, to locate the first three letters of a name, your function might be:

If A2 pointed to the first cell in the column of last names, use =LEFT(A2,3).

RILEY could be present in the cell, which would produce RIL. The function might be as follows if you used the RIGHT function to locate the state abbreviations in a list of cities:

if A2 pointed to the first cell in the cities column, then =RIGHT(A2,2)

The result would be “NY” if the cell contained the phrase “Brooklyn, NY.” “.

Please note that Indeed is not affiliated with any of the businesses mentioned in this article.

FAQ

Is there a way to truncate in Excel?

A truncated number is produced by the Excel TRUNC function using an optional number of digits. For example, TRUNC(4. 9) will return 4, and TRUNC(-3. 5) will return -3. The TRUNC function truncates as specified without rounding. number – The number to truncate.

How do I shorten a value in Excel?

1. Choose the numbers you want to shorten by right-clicking and choosing Format Cells from the context menu.
2. Click Number > Custom in the Format Cells dialog and enter [>999999]#,,”M”;#,”K” in the Type textbox. See screenshot:
3. To close the dialog, click OK. The large numbers are now condensed.

How do I truncate two columns in Excel?

1. Step 1: Prepare your data sheet. …
2. Step 2: Choose the cell or column where you want the text string to be truncated to appear.
3. Step 3: Enter the truncating formula to the RIGHT or LEFT of the target cell.

How do I round and truncate in Excel?

To round down, the formula is =ROUNDDOWN(num,digits) . When Excel truncates, it eliminates all rounding and removes a portion of the entered number. So if you input 345. The digits following the decimal point are removed when formatting the number 679 so that there are no decimal points.