 • 546K Members
• 2,182 Online
• 652K Conversations

Highlighted

# excel problem:want solution

I need help from experts! I wanna write a number like (1000000) across cells A1, B1&C1. For example . 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 . How is it possible? Please help me with your best efforts!
7 Replies
Highlighted

# Re: excel problem:want solution

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?

Highlighted

# Re: excel problem:want solution

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

Highlighted

# Re: excel problem:want solution

Many many thanks for your solution. It really works. I am so grateful to you.
Highlighted

# Re: excel problem:want solution

It was necessary to make a clearing house schedule. Amount should be written like that here. I hope, you really understand. Thanks a lot!
Highlighted

# Re: excel problem:want solution

I am happy to help!
Highlighted

# Re: excel problem:want solution

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
Highlighted

# Re: excel problem:want solution

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.

Related Conversations
Need Help Creating This Formula
James Reuland in Excel on
0 Replies
Excel Find and replace
Brickson_Dimas in Excel on
0 Replies