EXCEL TRUNCATE: Get Rid of Decimals From Numbers (+ Trim Text)

Excel is great with numbers. Whether you want to COUNT them, SUM them, round them, or simply change their data type, Excel can do it all.

But what if you only want to trim numbers? Or remove some decimal places from them? How do you do that?

Luckily, Excel offers the TRUNCATE function for this. It helps trim the digits of a number while keeping the original data intact.

If you want to know more about the TRUNC function, then read on. This guide has everything you need to know 🧐

The TRUNC function is easy to use, but a little practice can help you master it quickly. Download our sample workbook here to get a hands-on experience.

How to remove decimals from numbers (TRUNCATE function)

Removing decimals from numbers is extremely easy when you use the TRUNCATE function.

Give a number to the function – it will chop it down and return a truncated number as a result. Easy, no? πŸ˜€

Let’s now look at its syntax to understand the function better.

=TRUNC(number, [digits])

The number argument specifies the number or cell reference that is to be truncated.

The digits argument specifies the number of decimal points you want to remove. This is an optional argument.

Note that the TRUNC function doesn’t round or change the format of the number. However, the INT function rounds a number and eliminates the decimal places in one go. You can give it a shot.

Kasper Langmann, Microsoft Office Specialist

Let’s now see how the TRUNC function works in Excel.

Enter a number 5.946 in the TRUNC function in cell A2 as:

=TRUNC(5.946)

Excel TRUNC function in Excel worksheet

Press Enter.

Eliminating number of digits - nearest multiple

The TRUNC function returns a result 5.

That’s because when you don’t specify the second argument, the TRUNC function eliminates all the digits after the decimal point. And returns the integer part of a number πŸ€”

You can do the same using a cell reference.

  1. Select cell B1.
  2. Enter A2 in place of the first argument in the TRUNC function.
Following formula for Excel functions.
  1. Press enter.
Default value with positive numbers

And tada! You guessed it right – both arguments return the same results.

You’re getting better at it already πŸ˜‰

Now let’s use the TRUNC function with the second argument.

  1. Select cell B2.
  2. Enter the formula as:

=TRUNC(A2, 2)

Numeric value for the formula
  1. Press Enter
Number if decimal places - negative numbers

Notice that Excel returned the answer with two decimal places in the result.

That’s because we entered 2 in the formula for two decimal places. And Excel returns 5.79, which is exactly what we asked for.

It’s that easy to use the TRUNC function in Excel πŸ₯‡

We have added some more values for you to practice in the sample workbook.

How to truncate text in Excel

We now know how to truncate numbers in Excel. But how do you truncate text strings in Excel?

Unlike numbers, we use the LEFT or RIGHT function to truncate text strings in Excel.

Pro Tip!

You can use both these functions to truncate a text string. If the text you want to trim is on the left side of a cell, use the LEFT function and vice versa.

Let’s see it through an example below.

Say we want to extract the first three characters of the country Romania.

To do that:

  1. Select cell B2.
  2. Enter the formula as:

=LEFT(A2, 3)

Formula for specified number of decimal places
  1. Press Enter.
Result with LEFT formula

Excel returns the first three letters Rom just as we expected.

Isn’t that brilliant? 🀩

You can do the same for text on the right side.

Apply the RIGHT formula and enter the number of characters you want to extract – we entered three.

Press enter.

Result with RIGHT formula

Excel returns the last three characters of Romania – it’s that simple.

The remaining text strings are for you to practice πŸ€—

That’s it – Now what?

With the TRUNC function, you can slice and dice decimals in Excel like a pro. It’s incredibly easy to learn and can make your data a lot simpler.

Its best use is when you want to shorten a number to the nearest integer. And keep the original data intact all the same.

TRUNC in Microsoft Excel will be one of your favorite functions once you get equipped with its ins and outs. But if you want to round numbers along with removing decimals, try the INT function.

Luckily, Excel has a wide range of powerful functions similar to these two πŸ€“

Want to learn them already? We recommend you begin by practicing the VLOOKUP, IF, and SUMIF functions in Excel.

You can learn these fantastic functions for free in my 30-minute email course. It’s delivered right to your inbox. Join now! πŸ™Œ

Other resources

Did you enjoy reading this article? If yes, then you must want to read more.

Try these topics: Using the TRIM function in Excel, Replace TextExtract Text using LEFT and RIGHT functions, and more.

Frequently asked questions

Select a cell. In the formula, enter the cell reference of the number to be truncated and insert 2 in the second argument. Press enter. Excel returns the result with two decimal places only.

You can use the RIGHT function to trim any number of characters on the right side of a cell.

Select a cell. In the RIGHT formula, enter a cell reference containing the text string to be trimmed. And the second argument specifies the number of characters to be trimmed – enter 3.

Press Enter, and Excel will return the last three characters of the desired text string.