Aug 23 2019 04:44 AM
Aug 23 2019 08:27 AM
While there may be way to achive the results in the format you need, could you please share the reason for splitting a number in to three cells and adding , instead of simple addition in a range?
Aug 23 2019 08:41 AM
Hello @ferdaus,
As long as I understand correctly, you could do the following:
In cell A1 put your number (i.e. 1000000)
In cell C1 put =Left(A1,2)
In cell D1 put =Mid(A1,3,2)
In cell E1 put =Right(A1,3)
This will make C1=10, D1=00, E1=000
In cell A2 put your number (i.e. 2000000)
Highlight cells C1:E1
Copy the formula down to C2:E2
This will make C2=20, D2=00, E2=000
In cell C3, press Alt+=
This will sum the cells C1:C2
Copy the formula over to D3 and E3
Keep in mind that this method only works for numbers in the format 0000000.
Hope this helps!
PReagan
Aug 24 2019 10:29 PM
Aug 24 2019 10:32 PM
Aug 25 2019 06:38 AM
If you require numeric representations as opposed to text then the formulas
= QUOTIENT( amount, 100000 )
= MOD( QUOTIENT( ABS(amount), 1000 ), 100 )
= MOD( ABS(amount), 100 )
will divide a currency amount into Lakh, Thousands and Units.
Reversing the process to recombine the three digit groups can be done using
= SUM( {100000,1000,1} * ABS(D) )
Number formatting
0\,;-0\,;;
00\,
000\,
would ensure non-significant zeros are displayed and add commas if wanted.
Amount | L | T | U |
2640067 | 26, | 40, | 067 |
6754900 | 67, | 54, | 900 |
Aug 25 2019 08:38 AM
This is now just play time! I do not even know for certain that the problem is an Indian currency one. However, I decided to try for one formula using dynamic arrays to split the amount.
= MOD( QUOTIENT( amount, 10^units ), 10^digits )
and another to recombine the digit groups
= SUM( 10^units * ABS(groups) )
where the array constants 'units' and 'digits' are
={5,3,0}
={5,3,2}
respectively.