 # Splitting a number into integer and decimal portions

Share Here is a quick formula tip to start another awesome week.

Often while working with data, I need to split a number in to integer and decimal portions. Now, there are probably a ton of ways you can do this. But here are two formulas I use quite often and they work well.

Assuming the number is in cell A1,

• Integer part =INT(A1)
• Decimal part =MOD(A1,1)

These formulas work whenever my data has only positive numbers (which is the case 90% 0f time). But if I am dealing with a mix of positive and negative numbers, I use,

• Integer part =INT(A1) +(A1<0)=TRUNC(A1,0) [thanks to Somnath for suggesting this]
• Decimal part =MOD(A1,SIGN(A1))

### What formulas do you use to process numbers?

I use a lot of formulas while working with numbers. But my favorite ones are MOD, INT, SIGN, ABS, RAND and RANDBETWEEN (new in Excel 2007, requires Analysis toolpak in earlier versions).

### Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

### Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch. ### Sorting values in Olympic Medal Table style [Quick Tip]

It is Olympic season. Everyone I know is tracking the games and checking their country’s performance. One thing that we notice when looking at medal tally is,

A single Gold medal is worth more than any number of Silver medals. Like wise, a single Silver medal is worth more than any number of Bronze medals.

So, when you look at the ranking of countries, you see countries with single Gold medal higher up than countries with lots of Silver and Bronze medals (but no Gold).

## Related Tips

### 49 Responses to “Splitting a number into integer and decimal portions”

1. david says:

wat's the purpose of +(A1<0) ?

i traced it and it gave it as TRUE.

thanks!

2. Chandoo says:

@David: Excel treats TRUE as 1 and FALSE as 0. Since INT(negative number) will give a value 1 less than expected, I want to add 1 to it whenever a number is negative and 0 if the number is positive. So the expression (A1<0) will be TRUE or FALSE based on the sign of A1. Now, if you add this to another number, excel converts the TRUE to 1 and FALSE to 0.

Hope that makes sense.

3. sam says:

Number in A1
For Integers
In B1
=TRUNC(A1,0)
For Decimal
In C1
=A1-B1

4. Eamon says:

To split numbers (negative or positive) I use =TRUNC(A1,0) then to get the decimal =A1-A2 assuming =TRUNC(A1,0) is in cell A2.

5. Eamon says:

Woops - just saw Sam's post!

6. Pedro Wave says:

Formulas to split positive decimal numbers are used to separate dates into days and hours.

I like to operate with fractions typed as:
A1 = 0 5/8
A2 = 2 1/8
A3 = A1 + A2 = 2 3/4

7. Shashi says:

One of my friends had a requirement to separate X and Y coefficients from a string received from CAD software.
The string would look like x123.234y234.56.(X will be always in the first place but Y place is not fixed as it would depend on integer and Decimal places of X co-efficient)
Here is one of the solutions:
Assuming
Cell A1 -> x123.234y234.56
Enter Cells B1-> =FIND("x",A1,1) - to find the place of X in the string -(it will be always 1)
Enter CellsC1 ->=FIND("y",A1,1) - find Place of Y (position depends on X Coeff.)
Enter Cells D1 -> =MID(A1,B1+1,C1-2) - to get X coeff.
Enter Cells E1 -> =MID(A1,C1+1,LEN(A1)-C1)

Hope this would be helpful for those who take help of Excel while working on Mechanical design software.

• Karthik KN says:

There is a better and easier way :

Assuming Cell A1 contains : x123.234y234.56

Then

=MID(A1,2,FIND("y",A1,1)-2) - Would give you the X value
and
=MID(A1,FIND("y",A1,1)+1,LEN(A1)-FIND("y",A1,1)) - Would give you the Y Value

without much fuss 😉

• del says:

trying to build a worksheet where I plug in Numerous numbers down a column.
I take a reading of a 7.500” diameter. It is under size by tenths .0001
Could be more up to .002” so I want to just plug in a whole number and have the cell do the math. 7.500-.0015= 7.4985” what formula should I use

• Hui... says:

@Del

Assuming:
The Diameter is in B2 7.5
The Undersize is in B3 0.0015
In any other cell use: =B2-B3
Excel will display 7.4985
If the units are Inches you will need to apply a custom Number format
Select the cells
Ctrl 1
Number Tab
Custom
In the Type dialog enter: 0.0000\"
Excel will now display 7.4985"

8. Kalyan Chattopadhyay says:

Chandoo,
your blog (Splitting a number into integer and decimal portions) on these two formulas helps me a lot to solve a big problem at my office. Really awesome blog. Thanks Chandooo.

THANKS A LOOOOOOOOOOOOOOT.

Keep it up.

9. Prateek says:

Good, but whts the significance of typing 1(divisior) in case of decimal formula. I typed 2,3.... got the same result....

10. Chandoo says:

@Eamon & @Sam: Thanks for the tip on TRUNC(). I did not know about it earlier.

@Pedro: I use it for splitting days and time as well. Also, do you know you can show fractions in excel with custom format code - # ?/?

@Shashi..good tip, thanks for sharing.

@Kalyan: I am so glad you find this tip useful.

@Prateek: The result for 2, 3 will not be same as 1. Check it with multiple values. the fraction portion of a number is nothing but what remains after you remove nearest multiple of 1. (essentially mod(number,1) )

11. Somnath says:

Chandoo,

Since =INT(A1)+(A1<0) on a whole negative integer (with no decimal parts) E.g: -7.00 will return -6, I think the TRUNC function is more effective.

12. Raiyan says:

Hi Chandoo.....Can we apply more than one for formula for a cell or a selected range of cell???If we can then how could we...explain with a simple example.

Regards
Raiyan

13. Pedro Wave says:

Chandoo, using fraction format "As Hundreds" or custom format - # ?/100, decimal number -12.23 can be represented as -12 23/100.
This fraction format only split decimal numbers visually, but is awesome to me.

14. Ayan says:

Thanks Sam & Emon for trunc

15. Gaurav Jaswal says:

float a, c, d=0;
int c
while(a!=0)
{
c= a*10;
d= d*10 + c;
a=a*10 - c;
}

16. humphrey says:

How do I split \$84,000 in a 12 month period with july and August having equal amounts? What formula can I use? Thank you.

17. sophie says:

thanks, this helped me a lot with my formula!

18. krish says:

14-294,5,8,9,11,13-17

hi ,
in this number series how to take a total count in excel?

• Hui... says:

@Krish

Apologies, I have missed your question 🙁

What number should the series 14-294,5,8,9,11,13-17 return ?

19. John robinson says:

Hi Chandoo, I. Need to know what formula to use if I want the decimal values to add up separately ie 47.6+48.5=95.11. Every time the decimal value exceeds 9, it automatically increases the values left of the decimal. I do not want that. Kindly assist.

• Hui... says:

@John
Try:
=INT(A2)+INT(A3)+(MOD(A2,SIGN(A2))+MOD(A3,SIGN(A3)))/10

20. reza says:

I need an formula to change location of decimal and integer digit in a number in excel, like 21.3 => 3.12.

• reza says:

Hi
I find a below formula that can use in word
Using find and replace (Ctrl+H)
In find filed type this formula
([0-9]{1,}).([0-9]{1,})
In replace filed type this formula
\2.\1
and check Use wildcard and click replace all.

21. james says:

HI
what about these two areas, a number 92.45 , id like to grab part of the numbers from both sides, cant seem to work out how to combine them, e.g. 92.45 I want .45 to be as 45 ( solved as above in a post ) however what about 245 where the first number and the decimals are only looked at . I don't want he number 9 . also what about 81.7653 , I want to be able to grab only the last 3 digits of the decimal place, so it looks like 653?

any help on truncing this correctly would be good , thanks in advance.

22. GNG says:

Hi,

A1 cell is having integer part 12.00 and A2 Cell is having decimal part 0.34545. How can we concatenate in B1 Cell as 12.34545.

Rgds
GNG

• Hui... says:

@GNG
=Int(A1)+A2

23. 1178200010000920140701101456 says:

Please saparate these number in to different way like -

1178 20001 00009 20140701 101456

• Hui... says:

@Gujjala

Select the cell/s with the data
Goto Data, Text to Columns
Select Delimitered
Tick Space
Finish

Enjoy

24. sunny says:

I have below decimal numbers in excel
0.15
2.30
0.55
0.25
0.15
1.00
1.40
2.00
2.00
This is basically the total time spent, If i sum up these using the sumation in the excel i got the result as 9.80. But actual sum value is total 11.00 hours.

How can i get this sumation value as per hours and minutes

• Chandoo says:

Hi Sunny,

One option is to use below formula:

=SUMPRODUCT(INT(A1:A10)+ MOD(A1:A10,1)/0.6)

• sunny says:

I'm using open office. its giving the error Err:508

• sunny says:

Can I override the behaviour of sumation function in a way that when ever i select multiple cell(containing the decimal number), it adds all those numbers but decimal number should be added on the basis of 60 not the 100?

25. Francisco says:

Sunny, heres what worked for me.
=SUMPRODUCT(INT(A1:A10)+ MOD(A1:A10;1)/0.6)

26. Eric Guenette says:

The MOD(A1,SIGN(A1)) is good as long as A1 0. If A1 = 0 then you receive a #Div/0!

We could compensate for the possible 0 in your formula but at this point I think the classic way to calculate MOD is easier.
A1 - TRUNC(A1,0)

It work with positive, 0 and negative.

27. Eric Guenette says:

And if you are to use the MOD function with a different divider:
MOD(A1, x)

You could use :
A1 - ( TRUNC(A1/x,0) * x)

28. Jim says:

In a series of numbers, every time the decimal portions (MODs) is equal to or greater than "1", I want to add that "1" to its integer and continue accumulating the MODs until the next time they equal "1" and repeat.
Manual Example:
Totals 27.00 27.00 21.00 6.00
Need Formula
Month Repairs Cumul For This Column INT MOD
0 0.00 0.00 0 0.00 0.00
1 1.10 1.10 1 1.00 0.10
2 1.20 2.30 1 1.00 0.20
3 1.30 3.60 1 1.00 0.30
4 1.40 5.00 2 1.00 0.40
5 1.50 6.50 1 1.00 0.50
6 1.60 8.10 2 1.00 0.60
7 1.70 9.80 1 1.00 0.70
8 1.80 11.60 2 1.00 0.80
9 1.90 13.50 2 1.00 0.90
10 2.00 15.50 2 2.00 0.00
11 2.10 17.60 2 2.00 0.10
12 2.20 19.80 2 2.00 0.20
13 2.30 22.10 3 2.00 0.30
14 2.40 24.50 2 2.00 0.40
15 2.50 27.00 3 2.00 0.50

29. pbnec says:

For~
Integer part =INT(A1) +(A1<0)=TRUNC(A1,0)
, use~
Integer part =FLOOR(A1,SIGN(A1))
is better...

30. Jaffar says:

Hi,

I need to bring the value in a separate column which comes after 2016.

For ex : Jaffa hussain 11/12/2016 12345
Hussain 2016 456

From the above two i need to bring the values which are after 2016 in a separate cell

31. Jim Chrysomallis says:

hi every body i have a number 55585 in cell C3.
C3 is value which i put manually change every time to different number
how i can write a vba code to :
1st. divide the 54000 of 55585 with the divisor 500 and get the result 108 to A1 cell
and after
2nd. to divide the 1000 of 55585 with the divisor 100 and get the result 10 to A2 cell
and after
3rd. to divide the 500 of 55585 with the divisor 50 and get the result 10 to A3 cell
and after
4th. to divide the 75 of 55585 with the divisor 25 and get the result
3 to A4 cell
and after
5th. to divide the 10 of 55585 with the divisor 10 and get the result
1 to A5 cell

and the A1 cell is equal with 120 and to become 228 by adding the result 100 to A1
( eg A1=120 + 1st division result 108 =228)
from the first division.

and the A2 cell is equal with 100 and to become 110 by adding the result 10 to A2
( eg A2=100 + 2nd division result 10 =110) from the second division.

and the A3 cell is equal with 120 and to become 130 by adding the result 10 to A3
( eg A3=120 + 3rd division result 10 =130) from the third division

and the A4 cell is equal with 80 and to become 83 by adding the result 3 to A4
( eg A4=80 + 4th division result 3 =83) from the forth division

and the A5 cell is equal with 80 and to become 81 by adding the result 1 to A5
( eg A5=80 + 4th division result 1 =81) from the fifth division.

it means numbers bigger than 1000 as 54000 should divide with 500

it means numbers smaller or equal than 1000 as 1000 should divide with 100

it means numbers smaller or equal than 500 as 500 should divide with 50

it means numbers smaller or equal than 100 as 500 should divide with 25

it means numbers smaller or equal than 50 as 50 should divide with 10

32. Jim Chrysomallis says:

type mistace *the result 108 to A1 is correct at this follow eg

and the A1 cell is equal with 120 and to become 228 by adding* the result 100 to A1
( eg A1=120 + 1st division result 108 =228)
from the first division.

33. XL Test Plus and XLC says:

I went over this internet site and I think
you have a lot of superb information, saved to favorites (:
.

34. manoj says:

how to change value after decimal in next value
for example how to convert 1.2 to 1.4 into 2.0 value

• Hui... says:

@Manoj

Can you please explain what you mean by convert 1.2 to 1.4 into 2.0 value

maybe give an example

35. Kwanrat says:

Dear Sirs,

Thank you very much.

36. shardul says:

i want to split number 12345678 into two integers.
like int1 = 1234 & int2 = 5678.
can you plz suggest me the solution?

• Hui... says:

@Shardul
Assuming the Number is in cell A1
Assume in cell B1, Left half =Left(A1,Round(Len(A1),0)/2)
Assume in cell B1, Right half =Right(A1,Len(A1)-len(B1))

Enjoy

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.