Go Back

Get integer part of a number

We can use the TRUNC function to remove the decimal part of a number and only get the integer portion. This post provides an step by step guide on how we can get the integer of a number.

Figure 1: Using TRUNC to get integer portion of a number

Syntax of the formula

=TRUNC(number)

Number refers to the value that we want to find its integer. We can also use a cell reference in the place of number.

Explanation

The Excel TRUNC function is useful in slicing the decimal part of a number and returning its integer part. In our example above, the formula in B1 is as below;

=TRUNC(A1)

When we press Enter, the formula will slice off the decimal part (.4578) and only returns its integer (4).

Copying the formula across the other cells:

  • Select the cell with the formula, i.e. B1.
  • Point the cursor to the bottom-left corner of the cell, until we see a plus sign.
  • Hold the cursor down and drag it across the other cells.

Note, this function does not round off a number, it only removes the decimal portion.

We can supply a second argument to specify the precision of the truncation, though this is optional. When not supplied, the function treats it as zero. This means that the truncation will happen at the decimal point.

What of INT or ROUND functions?

With default settings, INT works the same way as TRUNC, when it comes to positive numbers. The only difference is that the INT function will round a number down to the next integer, before returning the integer portion of the number.

Example 2

=INT(number)

Figure 2: INT rounding off numbers away from zero

But for negative numbers, INT function deviates from how TRUNC works. The INT function rounds numbers down away from zero, irrespective of the decimal value.

If we want to round a number to the next integer, whether positive or negative, then use the ROUND function. This function looks as below;

=ROUND(number, 0)

Example 3

Figure 3: Using the ROUND function

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.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I have a lot of different part numbers for LEDs. I want to be able to enter the type of board, the flux type, the CRI, and the length and get a part number. Can you help?
Solved by M. H. in 17 mins
I'm trying to extract the customer part number form a table. The left column is out part number, the center column is the customer name and the right column is the customer part number. I want to select our part number and the customer name from a pair of lists and have the customer part number shown. The problem is that there are multiple customers for the our part number and duplicate customers for various other parts. I tried using vlookup but it only finds one customer number for the part. I can't get it to find the other customer part number. All of the customer part numbers are unique. I tried concatenating our part number and the customer name but even this will only find one customer part number. Is what I'm trying to do even possible? Thanks in advance for your help. Mike What other information do you need? 08501102 Delphi Beijing 28182403 08501102 Carter Fuel Systems 122-241 E 0850.3106 Carter Fuel Systems 122-256 A 0850.3106 SPI ROV005 4580.0100 Kautex Puebla 1001974001 4580.0100 Inergy Puebla 7410430AA 4590.0100 Kautex Puebla 1001973001 4590.0100 POIA Puebla 7410431AA 5140.0100 Bosch Brazil F000.TE1.0J7 5140.0100 Carter Fuel Systems 122-242 F 5690.0100 SPI ROV007 5690.0100 Delphi Mexico 28182406 5930.0100 Carter Fuel Systems 122-239 D
Solved by E. B. in 30 mins
trying to get time to display as an integer. Formula is an array
Solved by V. Q. in 25 mins

Leave a Comment

avatar