number formatting

[GBOB]

Hi all,

Can anyone assist me in breaking apart a currecy formatted cell into dollars and cents? (have a second question below as well)

For example I have $23.12 in cell a1, and would like to break it into $21 in cell a2 and $.12 in cell a3? All the various formatting functions such as Left, right, etc. seem to get hung up if I have a figure such as $1100.00 they won't recognize the 0s?

My second question is how to break apart a number into each of its digits. For example, break the number 56346 in cell b1, into 5 in cell b2, 6 in cell b3, 3 in cell b4, etc...? Can figure out how using a very convoluted method, but must be an easier way?

Thanks for the help!

[Asala42]

Hey,

For example I have $23.12 in cell a1, and would like to break it into $21 in cell a2 and $.12 in cell a3? All the various formatting functions such as Left, right, etc. seem to get hung up if I have a figure such as $1100.00 they won't recognize the 0s?

In A2: =ROUNDDOWN(A1,0)

In A3: =A1-A2

Hope that helps,
Adam

[Aladin Akyurek]

On 2002-05-20 11:42, GBOB wrote:
Hi all,

Can anyone assist me in breaking apart a currecy formatted cell into dollars and cents? (have a second question below as well)

For example I have $23.12 in cell a1, and would like to break it into $21 in cell a2 and $.12 in cell a3? All the various formatting functions such as Left, right, etc. seem to get hung up if I have a figure such as $1100.00 they won't recognize the 0s?

My second question is how to break apart a number into each of its digits. For example, break the number 56346 in cell b1, into 5 in cell b2, 6 in cell b3, 3 in cell b4, etc...? Can figure out how using a very convoluted method, but must be an easier way?

Thanks for the help!

Q1:

In B1 enter:

=IF(INT(A1)=A1,A1,LEFT(A1,SEARCH(".",A1)-1)+0)

In C1 enter:

=IF(INT(A1)=A1,0,("0"&RIGHT(A1,LEN(A1)-SEARCH(".",A1)+1))+0)

Q2:

In B1 enter and copy across:

=IF(COLUMN()-1<=LEN($A1),MID($A1,COLUMN()-1,1)+0,"")

[GBOB]

Thank you both for your responses. I used Aladin's suggestion and it worked perfectly. Big thanks!!

[Seti]

You can use the INT function instead of the ROUNDDOWN function too if you don't have negative numbers.

As far as breaking numbers apart, the formulas are a bit more complicated. Assume you have 54321 in cell A1.

B1: (to get the 5) ROUNDDOWN(A1,-4)/10000
C1: ROUNDDOWN(A1,-3)/1000-10*B1
D1: ROUNDDOWN(A1,-2)/100-100*B1-10*C1
E1: ROUNDDOWN(A1,-1)/10-1000*B1-100*C1-10*D1
F1: A1-10000*B1-1000*C1-100*D1-10*E1