excel problem:want solution

Copper Contributor
I need help from experts! I wanna write a number like (1000000) across cells A1, B1&C1. For example [10][00][000]. Again in the next row I wanna write another number like (2000000) across cells A2, B2 & C2. Then, I wanna write more numbers in the below. At last I wanna sum all numbers across 3 cells like [30][00][000]. How is it possible? Please help me with your best efforts!
7 Replies

@ferdaus 

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?    

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

 

Many many thanks for your solution. It really works. I am so grateful to you.
It was necessary to make a clearing house schedule. Amount should be written like that here. I hope, you really understand. Thanks a lot!
I am happy to help!

@ferdaus 

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.

 

AmountLTU
264006726,40,067
675490067,54,900

@ferdaus 

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.