Break a Whole Number in to Billions, Millions, Thousands, Hundreds, Tens Ones

[dramnath1981]

Hi,


Please help me in converting a number in to the following format.


Eg: 368002


Solution expected

300000

60000

8000

2


Eg: 300002

Solution expected

300000

2


Assumptions:

- The sum of the broken down numbers should be equal to the original value

- There won't be any decimals

- Results can be displayed in different cells (say 30000 in B1, 6000 in B2, 8000 in B3)


Any solution, either through Excel / VBA would be of great help. Its a kind of urgent.


Thanks guys for all your help.


Regards,

Ramnath

 

[TessaES]

If your number is in B1:

in B2: =MOD(B1,10)

in B3: =MOD(B1,100)-B2

in B4: =MOD(B1,1000)-sum(B2:B3) etc.

 

[Hui]

Ramnath

What about the following


B2: =LEFT(B1,1)*POWER(10,LEN(B1)-1)

B3: =LEFT($B$1-SUM($B$2:B2),1)*POWER(10,LEN($B$1-SUM($B$2:B2))-1)


copy B3 down


This solution will scale to any size number automatically, just copy B3 down as many rows as there are digits


If you don't want the trailing Zero's, Change

B3: =IF(SUM($B$2:B2)=$B$1,"",LEFT($B$1-SUM($B$2:B2),1)*POWER(10,LEN($B$1-SUM($B$2:B2))-1))

 

[dramnath1981]

Hui,


You are real excel magician. I got my result using your formula.


Thanks a lot for your help.


Regards,

Ramnath

 

[dramnath1981]

Hui,


I tried something in a little round about way. Out of sheer eagerness to get a pat from you


Cell A1 holds the value 36800008


Below are Formula Cells

Cell B1 =IFERROR(IF(LEFT(A1,1)="0","",(LEFT(A1,1)&(REPT("0",(LEN(A1)-1))))),"")

Result in B1 - 30000000


Cell C1 =IFERROR(IF(MID(A1,2,1)="0","",(MID(A1,2,1)&(REPT("0",(LEN(A1)-2))))),"")

Result in C1 - 600000


Cell D1 =IFERROR(IF(MID(A1,3,1)="0","",(MID(A1,3,1)&(REPT("0",(LEN(A1)-3))))),"")

Result in D1 - Null


Cell E1 =IFERROR(IF(MID(A1,4,1)="0","",(MID(A1,4,1)&(REPT("0",(LEN(A1)-4))))),"")

Result in E1 - Null


Cell F1 =IFERROR(IF(MID(A1,5,1)="0","",(MID(A1,5,1)&(REPT("0",(LEN(A1)-5))))),"")

Result in F1 - Null


Cell G1 =IFERROR(IF(MID(A1,6,1)="0","",(MID(A1,6,1)&(REPT("0",(LEN(A1)-6))))),"")

Result in G1 - Null


Cell H1 =IFERROR(IF(MID(A1,7,1)="0","",(MID(A1,7,1)&(REPT("0",(LEN(A1)-7))))),"")

Result in H1 - 8


The only problem in the above approach is inbetween you will get blank cells and you have avoided that problem using your brilliant skills.


Regards,

Ramnath

 

[Hui]

Ramnath

You were close with Rept

you could do it like


A2: =VALUE(LEFT(A1,1)&REPT("0",LEN(A1)-1))

A3: =IF(SUM($A$2:A2)=$A$1,"",VALUE(LEFT(($A$1-SUM($A$2:A2)),1)&REPT("0",LEN($A$1-SUM($A$2:A2))-1)))


and copy down