Breaking Up Numbers

[Minitman]

Greetings,

I am attempting to get an 8 digit number from a cell on one sheet and
enter it into 8 cells on a second sheet.

Example: In cell C1 on Sheet 1 is the number $876543.21 which I need
to break up into cells D1 through D8 (D1=8, D2=7, D3=6, D4=5, D5=4,
D6=3, D7=2 and D8=1).

How is this done?

Any one have an idea?

Any help would be most appreciated.

-Minitman

 

[Bob Umlas]

In cell D2 enter =MID(100*Sheet1!$C$1,ROW(),1) and fill down to D8

Bob Umlas
Excel MVP

 

[Minitman]

Hey Bob,

I think I messed up!

I meant to go across, not down.

I just tried this and I could not get it to work!

One other item, the number is not a fixed size. It can be from 0.01
to 1000000.00 (with 2 decimal places with no commas).

How would I do that?

-Minitman

 

[Minitman]

Hey Bob,

I could not get MID to work. I did, however, get RIGHT to work -
almost. I was missing the 100* the number to convert it to an
integer, that was a big help - Thanks.

Here's the code I came up with -

For the 10,000,000;s place located at D1:

=IF((RIGHT(100*Sheet1!$C$1,10)-RIGHT(100*Sheet1!$C$1,9))/100000000=0,"",(RIGHT(100*Sheet1!$C$1,10)-RIGHT(100*Sheet1!$C$1,9))/100000000)

For the 1,000,000's place located at E1:

=IF(AND(D1="",(RIGHT(100*Sheet1!$C$1,9)-RIGHT(100*Sheet1!$C$1,8))/100000000=0),"",(RIGHT(100*Sheet1!$C$1,9)-RIGHT(100*Sheet1!$C$1,8))/100000000)

For the 100,000's place located at F1:

=IF(AND(D1="",E1="",(RIGHT(100*Sheet1!$C$1,8)-RIGHT(100*Sheet1!$C$1,7))/10000000=0),"",(RIGHT(100*Sheet1!$C$1,8)-RIGHT(100*Sheet1!$C$1,7))/10000000)

For the 10,000's place located at G1:

=IF(AND(D1="",E1="",F1="",(RIGHT(100*Sheet1!$C$1,7)-RIGHT(100*Sheet1!$C$1,6))/1000000=0),"",(RIGHT(100*Sheet1!$C$1,7)-RIGHT(100*Sheet1!$C$1,6))/1000000)

For the 1,000's place located at H1:

=IF(AND(D1="",E1="",F1="",G1="",(RIGHT(100*Sheet1!$C$1,6)-RIGHT(100*Sheet1!$C$1,5))/100000=0),"",(RIGHT(100*Sheet1!$C$1,6)-RIGHT(100*Sheet1!$C$1,5))/100000)

For the 100's place located at I1:

=IF(AND(D1="",E1="",F1="",G1="",H1="",(RIGHT(100*Sheet1!$C$1,5)-RIGHT(100*Sheet1!$C$1,4))/10000=0),"",(RIGHT(100*Sheet1!$C$1,5)-RIGHT(100*Sheet1!$C$1,4))/10000)

For the 10's place located at J1:

=IF(AND(D1="",E1="",F1="",G1="",H1="",I1="",(RIGHT(100*Sheet1!$C$1,4)-RIGHT(100*Sheet1!$C$1,3))/1000=0),"",(RIGHT(100*Sheet1!$C$1,4)-RIGHT(100*Sheet1!$C$1,3))/1000)

For the 1's place located at K1:

=IF(AND(D1="",E1="",F1="",G1="",H1="",I1="",J1="",(RIGHT(100*Sheet1!$C$1,3)-RIGHT(100*Sheet1!$C$1,2))/100=0),"",(RIGHT(100*Sheet1!$C$1,3)-RIGHT(100*Sheet1!$C$1,2))/100)

For the 1/10's place located at L1:

=IF(AND(D1="",E1="",F1="",G1="",H1="",I1="",J1="",K1="",(RIGHT(100*Sheet1!$C$1,2)-RIGHT(100*Sheet1!$C$1,1))/10=0),"",(RIGHT(100*Sheet1!$C$1,2)-RIGHT(100*Sheet1!$C$1,1))/10)

For the 1/100s place located at M1:

=IF(AND(D1="",E1="",F1="",G1="",H1="",I1="",J1="",K1="",L1="",RIGHT(100*Sheet1!$C$1,)=0),"",RIGHT(100*Sheet1!$C$1,1))

It works with 2 decimal places. When I tried to use it on a cell
formatted for 2 decimal places but having more, I discovered that the
formatting does not go through the link!

Does anyone know how to convert a number with an unknown number of
places to the right of the decimal to exactly 2 places?

Any help would be appreciated.

-Minitman

 

[Ron Rosenfeld]

Does anyone know how to convert a number with an unknown number of
places to the right of the decimal to exactly 2 places?

=INT(C1*100) or =ROUND(C1*100)

I'm not sure of the range of your cells.

If you will always start in Sheet2!D1 then you might be able to use this
formula in that cell: =MID(INT(Sheet1!$C1*100),COLUMN()-3,1)

and copy/drag to the right as far as necessary.

That will always work with an eight digit number.

If the numbers can be less than eight digits, but you always want D1 to
represent 10^5, then use:

=MID(TEXT(Sheet1!$C1*100,"00000000"),COLUMN()-3,1)

It will place 0's in the initial cells if the numbers are 10^4 or less. Also,
it will ROUND a number with more than two places to the right of the decimal,
so 23.566 will become 0 0 0 0 2 3 5 7

Since I'm not sure of the range of your data or exactly what and where you want
your results, you may need to alter the above formulas slightly to get what you
really want. But one of them should do the trick.


--ron

 

[Minitman]

Hey Ron,

That works better then what I came up with!

Thank you

-Minitman

 

[Ron Rosenfeld]

That works better then what I came up with!

Thank you

-Minitman

Thank you for the feedback.

--ron