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

#### dramnath1981

##### Member
Hi,

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

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

##### Member
Hui,

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

Thanks a lot for your help.

Regards,

Ramnath

#### dramnath1981

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