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.
Table of Contents
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.
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.
Let’s now see how the TRUNC function works in Excel.
Enter a number 5.946 in the TRUNC function in cell A2 as:
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.
- Select cell B1.
- Enter A2 in place of the first argument in the TRUNC function.
- Press enter.
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.
- Select cell B2.
- Enter the formula as:
- Press Enter
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.
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:
- Select cell B2.
- Enter the formula as:
- Press Enter.
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.
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! 🙌
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 Text, Extract Text using LEFT and RIGHT functions, and more.