Splitting a number into integer and decimal portions
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).
What about you? What formulas do you use and how do you process your numbers? Please share using comments.
Learn more quick tips & formula examples.
Do you want to be awesome in Excel?
Here is a smart way to become awesome in Excel. Just signup for my Excel newsletter. Every week you will receive an Excel tip, tutorial, template or example delivered to your inbox. What more, as a joining bonus, I am giving away a 25 page eBook containing 95 Excel tips & tricks. Please signup below:
Your email address is safe with us. Our policies
More awesome tips for you:
 

Leave a Reply
VLOOKUP Formula Cheatsheet – FREE Download  An Excel Dashboard to Visualize 10,007 Comments [Dashboard Tutorial] 
37 Responses to “Splitting a number into integer and decimal portions”
interested to know about this =INT(A1) +(A1<0)
wat's the purpose of +(A1<0) ?
i traced it and it gave it as TRUE.
thanks!
@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.
Number in A1
For Integers
In B1
=TRUNC(A1,0)
For Decimal
In C1
=A1B1
To split numbers (negative or positive) I use =TRUNC(A1,0) then to get the decimal =A1A2 assuming =TRUNC(A1,0) is in cell A2.
Woops – just saw Sam’s post!
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
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 coefficient)
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,C12) – 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.
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 š
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.
Good, but whts the significance of typing 1(divisior) in case of decimal formula. I typed 2,3…. got the same result….
@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) )
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.
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
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.
Thanks Sam & Emon for trunc
float a, c, d=0;
int c
while(a!=0)
{
c= a*10;
d= d*10 + c;
a=a*10 – c;
}
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.
thanks, this helped me a lot with my formula!
14294,5,8,9,11,1317
hi ,
in this number series how to take a total count in excel?
@Krish
Apologies, I have missed your question
What number should the series 14294,5,8,9,11,1317 return ?
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.
@John
Try:
=INT(A2)+INT(A3)+(MOD(A2,SIGN(A2))+MOD(A3,SIGN(A3)))/10
Hi. Thanks for your post.
I need an formula to change location of decimal and integer digit in a number in excel, like 21.3 => 3.12.
Hi
I find a below formula that can use in word
Using find and replace (Ctrl+H)
In find filed type this formula
([09]{1,}).([09]{1,})
In replace filed type this formula
\2.\1
and check Use wildcard and click replace all.
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.
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.
Please help me…
Thanks in Advance
Rgds
GNG
@GNG
=Int(A1)+A2
Please saparate these number in to different way like –
1178 20001 00009 20140701 101456
@Gujjala
Select the cell/s with the data
Goto Data, Text to Columns
Select Delimitered
Tick Space
Finish
Enjoy
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
Hi Sunny,
One option is to use below formula:
=SUMPRODUCT(INT(A1:A10)+ MOD(A1:A10,1)/0.6)
I’m using open office. its giving the error Err:508
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?
Sunny, heres what worked for me.
=SUMPRODUCT(INT(A1:A10)+ MOD(A1:A10;1)/0.6)
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.
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)
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