Breaking up Number Into Hundreds Thousands and Millions

[dubroval]

Hi All

How would I break up a number like 456,056,123 into 3 cells - one for Hundreds, Thousands, and Millions using functions only ?

Thanks

Alex

 

[Legacy 68403]

try useing the functions left,mid and right. For example if the cell A1 has the value 456,056,123 try the function

=left(A1,3)*1000000 in cell B1
=mid(A1,4,3)*1000 in cell C1
=right(A1,3) in cell D1

 

[barry houdini]

For your example what should the resultant cells contain?

 

[Boller]

Also, do the numbers always consist of exactly 9 digits?

 

[PaddyD]

notwithstanding answers to questions asked:

millions portion:

=ROUNDDOWN(MOD(A1,1000000000),-6) or =ROUNDDOWN(MOD(A1,10^9),-6)

thousands:

=ROUNDDOWN(MOD(A1,1000000),-3) or =ROUNDDOWN(MOD(A1,10^6),-3)

hundreds:

=ROUNDDOWN(MOD(A1,1000),0) or =ROUNDDOWN(MOD(A1,10^3),0)


...amongst other approaches

 

[dubroval]

To clarify ....

It can be any number, not just 9 digits and can be negative or positive.

Because we are required to submit data in a predefined format, they are requesting that we have columns for hundreds, thousands, millions and yes even billions. So if a number if -123,456 then billions and millions would be zero, thousands would be -123 and hundreds would be -456.

Thanks,

Alex

 

[barry houdini]

If your figure is in A1

For hundreds

=MOD(ABS(A1),1000)*SIGN(A1)

thousands

=FLOOR(MOD(ABS(A1),10^6),10^3)*SIGN(A1)

millions

=FLOOR(MOD(ABS(A1),10^9),10^6)*SIGN(A1)

and assuming those three formula are in B1,C1 and D1

billions

=A1-B1-C1-D1