Want to separate number and decimal. After need to sum of number & decimal.

[kumarp11]

Hi,

I want to separate number and decimal. After that need the sum of number & decimal.

Used the formula (suppose cell value A1 = 10.3 in general format)
Number Cell B1 = INT(A1)
Decimal Cell C1 = MOD(A1,2) * 10 >>> To make the decimal to number multiply by 10

Cell D1 = B1+C1 <<Answer is 13>>

Problem starts when I put the value in cell A1 as 10.30, it gives me the same answer of 10.3. But I want the answer should be 10+30 = 40.

I can't change the format to number due to some reasons.

[AlKey]

May this


in B1

=INT(A1)

in C1

=MOD(A1,1)*100

in D1

=SUM(B1,C1)

	A	B	C	D
1	10.3	10	30	40

if you want this in one formula

=SUM(INT(A1),MOD(A1,1)*100)

[hemesh]

or you can try below in B1 to get the sum
=SUMPRODUCT(--(MID(SUBSTITUTE("."&A1,".",REPT(" ",250)),250*ROW(INDIRECT("1:"&(LEN(A1)-LEN(SUBSTITUTE(A1,".","")))+1)),250)))

[kumarp11]

Thanks Hemesh

Its working now

or you can try below in B1 to get the sum
=SUMPRODUCT(--(MID(SUBSTITUTE("."&A1,".",REPT(" ",250)),250*ROW(INDIRECT("1:"&(LEN(A1)-LEN(SUBSTITUTE(A1,".","")))+1)),250)))

[kumarp11]

Hemesh,

One small query in this....

I want to multiply the integer value by 12 (i.e., if value is 4.10 then answer should be = (4*12)+10 = 58)

Can you help on this.

[hemesh]

if you have one decimal point in you cell then try below
=(LEFT(A1,FIND(".",A1)-1)+0)*12+(RIGHT(A1,LEN(A1)-FIND(".",A1))*10)

for multiple decimals like
10.12.15.1897.123.15874 you can use earlier one