spread the number 123.45 so each digit goes to a different cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

If the answer to a formula is 123.45 and I want to then break that number up
so that each digit goes into a different cell....

ie.... 1 goes in one cell, 2 goes in the next cell, 3 goes in the next
cell, I can skip the decimal, 4 goes in the next cell and 5 goes in the last
cell.

What I am trying to do is take the answer to the formula and make it appear
on a printed form with each digit in a separate box......

Also, my answer might be in the thousands or only in the hundreds, what will
appear in the emply boxes. I have attempted using (right) and (left) but I
don't know how to pick only one number. and i don't know what to do with the
empty boxes. Any help would be appreciated.....

Thanks Linda H.
 
Linda

if the number were in cell A11, for example, one way to get the values into
cells B11, C11, etc is:

=MID($A11,COLUMN()-1,1)

Put the formula in B11 and drag across

That does put the decimal point in so it might not be exactly what you want.

To lose the decimal point, try:

=IF(COLUMN()-1<FIND(".",$A11),MID($A11,COLUMN()-1,1),MID($A11,COLUMN(),1))

Regards

Trevor
 
Well, you can do that, or you can simply use the "text to columns" function
at the "data" menu (choosing
If you are dinamically generating this values, you could macro this
operation.

Another way to do this, using formula could be:
=left(A1;1)
and then =right(left(A1;2);1) and then make the count run while you drag
this. The second parameter in the "left" (or "right") function should
increase. If they don´t, make a reference to a series that do.

Regards,
Albion - Argentina
 
why not try <data-texttocolumn-fixedwidth> in the next window create the
break lines whereever you want. ofcourse decimal point also will be in one
cell.
 
Number is in A1
In B1 (for thousands) =IF(A1>1000,INT(A1/1000),"")
In C1 (for hundreds) =IF(A1>100,INT(MOD(A1,1000)/100),"")
In D1 (for tens) =IF(A1>10,INT(MOD(A1,100)/10),"")
In E1 (for units) =IF(A1>=1,INT(MOD(A1,10)),"")
In F1 (for first decimal) =INT(MOD(A1,1)*10)
In G1 (second decimal) =INT(MOD(A1,1)*100)-10*F1
I expect there a few dozen other math ops to do this!
 
This modified formula caters for no decimal point and also forces the
results to be numeric as opposed to text numbers

=IF(ISERROR(FIND(".",$A11)),--MID($A11,COLUMN()-1,1),IF(COLUMN()-1<FIND(".",$A11),--MID($A11,COLUMN()-1,1),--MID($A11,COLUMN(),1)))

Regards

Trevor
 
Back
Top