• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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

New Member
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

Excel Ninja
Staff member
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))
 
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

Excel Ninja
Staff member
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
 
Top