Truncating a number means decreasing the number of digits from the decimal section or from the right side of that number in mathematics. In Excel, sometimes after any calculation, we get a result with large digits in the decimal section. Maybe all the digits are not required. We may require a certain number of digits, or we want the integer part only. Excel has some dedicated functions to perform those tasks. There are also other ways to truncate numbers in Excel. In this article, we will discuss all the methods to truncate numbers in excel with proper illustrations.

**Table of Contents**hide

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**8 Methods to Truncate Numbers in Excel**

We can truncate numbers from the left or right sides in Excel. There are also options to **truncate texts in Excel**.

We will truncate numbers using the following dataset. Have a look at the below section for all the methods.

**1. Use of TRUNC Function to Truncate Numbers**

**TRUNC function**

**truncates a number to an integer by removing the decimal, or fractional, part of the number.**

Here, we will use the **TRUNC** function. This function can remove all the decimals at a time. Besides, we can also keep a certain number of decimal digits in the numbers. Moreover, there is an option to shorten any number.

**📌 ****Steps:**

- Put the following formula based on the
**TRUNC**function on**Cell C5**.

`=TRUNC(B5)`

We can see all the decimal digits have been removed from the number.

There is another option in the **TRUCN** function. We can keep the desired number of decimal digits. Then put that desired number in the formula.

- Look at the formula below used in
**Cell D5**.

`=TRUNC(B5,1)`

We put **1** in the formula. It means **1** decimal digit will exist in the result.

- We can also shorten the value of a number using this
**TRUNC**function. - Look at the following formula of
**Cell E5**.

`=TRUNC(B5,-1)`

We got **10** instead of **15**. Because we put **-1** in the formula. This reduces the last digit of the integer part of the given number and makes it **0** (“**Zero**“). If we put **-2**, that will transform the last two digits of the integer to **0**.

**Read More: ****How to Truncate Decimal in Excel (6 Easy Ways)**

**2. Use of INT Function**

**INT function**rounds a number down to the nearest integer.

The **INT **function just separates the integer part of a number. It is possible to keep any decimal digits when using this **INT** function.

**📌 ****Steps:**

- Look at the following formula used in
**Cell C5**.

`=INT(B5)`

We can see all the numbers truncated by removing the decimal successfully.

**3. Use of ROUND Function to Round Off Numbers**

**ROUND function**rounds a number to a specified number of digits.

The **ROUND **function rounds off or down a number to a specified number of decimals that mention in the formula. We are not allowed to keep this decimal place argument empty.

**📌 ****Steps:**

- Look at the following formula of
**Cell C5**.

`=ROUND(B5,1)`

We put **1** in the decimal place. So, we get **1** digit in the decimal section.

If we do not want to remove all the digits from the decimal section, put **0** removing **1**.

- Look at the following formula.

`=ROUND(B5,0)`

All the decimal digits have been removed.

**Similar Readings**

**How to Turn Off Flash Fill in Excel (2 Easy Methods)****Flash Fill Not Recognizing Pattern in Excel (4 Causes with Fixes)****How to Use Flash Fill in Excel to Split Data (4 Quick Ways)**

**4. ****Truncate Numbers by Rounding Up**

**ROUNDUP function**rounds a number up, away from zero.

In the previous sections, we truncated the number by the round-down process. But in this section, we will truncate by rounding up the numbers. We will use the **ROUNDUP** function here.

**📌 ****Steps:**

- Look at the formula used on
**Cell C5**.

`=ROUNDUP(B5,1)`

Here, **15.32** is truncated by rounding up to **15.4**. One decimal digit is shown because we put **1** in the second argument.

- If we want to remove all the decimal digits, put
**0**on the**2nd**Then, the formula becomes:

`=ROUNDUP(B5,0)`

It rounds up numbers and returns the integer part only.

**5. Remove Numbers from the Right and Left Sides**

The **RIGHT function **returns the specified number of characters from the end of a text string.

The **LEFT function** returns the specified number of characters from the start of a text string.

The **FIND function** returns starting position of one text string within another text string. This function is case-sensitive.

The **LEN function** returns the number of characters in a text string.

Previously, we showed how to remove digits from the right side only. Here, we will remove digits from the right and left on both sides.

**📌 ****Steps:**

- First, we will use a combination of the
**LEFT**and**FIND**functions to remove decimal digits from the right side. Look at the formula of**Cell C5**.

`=LEFT(B5,FIND(".",B5)-1)`

We get the integer numbers only.

- Now, we remove digits from the right side. For that, we will use a combination of
**RIGHT**,**LEN**, and**FIND**Put the following formula on**Cell D5**.

`=RIGHT(B5,LEN(B5)-FIND(".",B5))`

Here, get the integer numbers only. We separated them from the integer part.

**Formula Explanation:**

**FIND(“.”,B5)**

We want to find the position of the **dot (.)** in **Cell B5**.

**Result: 3**

**LEN(B5)**

We want to find out the length of **B5**.

**Result: 32**

**LEN(B5)-FIND(“.”,B5)**

A subtraction operation is applied here.

**Result: 2**

**RIGHT(B5,LEN(B5)-FIND(“.”,B5))**

This returns the **2** digits from the right side.

**Result: 32**

**Read More: ****How to Stop Excel from Truncating Text (3 Easy Methods)**

**6. Combination of REPLACE, FIND & LEN Functions**

**REPLACE function**replaces a part of a text string with a different text string.

Here, we will use the combination of the **REPLACE**, **FIND** & **LEN** functions to remove numbers from the left side.

**📌 ****Steps:**

- Put the following formula on
**Cell C5**.

`=REPLACE(B5,FIND(".",B5),LEN(B5)-FIND(".",B5)+1,"")`

All the decimal digits have been truncated here.

We can also use the **SEARCH function** despite the **FIND** function.

`REPLACE(B5,SEARCH(".",B5),LEN(B5)-SEARCH(".",B5)+1,"")`

**Formula Explanation:**

**FIND(“.”,B5)**

It finds the position of the **dot(.)** in **B5**.

**Result: 3**

**LEN(B5)**

Determines the length of **B5**.

**Result: 5**

**LEN(B5)-FIND(“.”,B5)+1**

Addition and subtraction operations are applied here.

**Result: 3**

**REPLACE(B5,FIND(“.”,B5),LEN(B5)-FIND(“.”,B5)+1,””)**

Replace operation is applied using the values of the previous section.

**Result: 15**

**Read More: ****Splitting Text in Excel (8 Useful Applications)**

**Similar Readings**

**How to Truncate Date in Excel (4 Simple Methods)****Creating Email Addresses from a Single Column with Flash Fill, TEXT Formulas & Commentator’s Text Formula Suggestions****How to Truncate Text After Character in Excel (4 Suitable Ways)**

**7. Use of Excel Flash Fill Feature to Truncate Numbers**

The **Flash Fill** is a wonderful feature of Excel. It follows the given sample or sequence and applies that to the rest of the cells.

**📌 ****Steps:**

- In the
**1st**column of the dataset, numbers are given. We want to get only the integers. So, type**15**on**Cell C5**. - Then, click on the
**Flash Fill**option of the**Data**tab.

- Look at the dataset.

Decimal digits are truncated from the numbers. We can also use a simple Keyboard shortcut **Ctrl + E**.

**Read More: ****[Solved!] Flash Fill Not Working in Excel (5 Reasons with Solutions)**

**8. Use of Text to Columns Wizard**

There is another option in Excel to truncate numbers without a formula. This is the **Text to Columns** feature.

**📌 ****Steps:**

- First, select the data range of the
**Result**column. - Go to the
**Data**tab. - Choose the
**Text to Columns**feature from the**Data Tools**group.

**Step 1**of**Convert Text to Columns Wizard**window.- Choose
**Delimited**and then click on the**Next**button.

- In
**Step 2**, mark the**Other**option and put a**dot(.)**on the box. - Again, click the
**Next**button.

- In
**Step 3**, we will skip one column. Then choose the**Do not import column (skip)**. - Finally, press the
**Finish**button.

- Look at the dataset.

We can see the decimal part has been removed.

**How to Truncate Text in Excel**

Previously, we showed how to truncate numbers in Excel. Now, we will show how to truncate text in Excel. We will use the combination of **LEFT** and **FIND** functions and truncate the **2nd** part of the text.

**📌 ****Steps:**

- Put the following formula on
**Cell C5**.

`=LEFT(B5,FIND(" ",B5)-1)`

We can see in the **Result **column only the** 1st **part is showing.

**Read More: ****How to Truncate Text from Right in Excel (5 Easy Methods)**

**Conclusion**

In this article, we described how to truncate numbers in Excel with and without a formula. We also showed how to truncate texts in Excel. I hope this will satisfy your needs. Please have a look at our website **ExcelDemy** and give your suggestions in the comment box.