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

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


"Linda H." <Linda wrote in message
...
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.

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

"Trevor Shuttleworth" escribió en el mensaje
...
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


"Linda H." <Linda wrote in message
...
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.

Albion

have you tested this method? It didn't work for me.

Regards

Trevor


"Albion" wrote in message
...
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

"Trevor Shuttleworth" escribió en el mensaje
...
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


"Linda H." <Linda wrote in message
...
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.

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.


Trevor Shuttleworth wrote in message
...
Albion

have you tested this method? It didn't work for me.

Regards

Trevor


"Albion" wrote in message
...
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

"Trevor Shuttleworth" escribió en el mensaje
...
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


"Linda H." <Linda wrote in message
...
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.

Number is in A1
In B1 (for thousands) =IF(A11000,INT(A1/1000),"")
In C1 (for hundreds) =IF(A1100,INT(MOD(A1,1000)/100),"")
In D1 (for tens) =IF(A110,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!
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Linda H." <Linda wrote in message
...
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.

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


"Trevor Shuttleworth" wrote in message
...
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


"Linda H." <Linda wrote in message
...
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.