Extracting dollars and cents

G

Guest

I have a task which I would assume would be really simple, but I am going
thru hoops to get this done, and there MUST be a simpler way. What I want to
do is separate and extract the dollars and cents from one figure. For
example, $1000.07, I need to extract this (for forms) to 2 separate
numbers...1000 (the dollar side) and 07 (the cent side WITHOUT a decimal
point, and must show 07, not just 7)

The dollar side is easy...I just use the TRUNC command, but the cents side
is painful because Excel calculates out to x decimal points so I can't use
the RIGHT command. What I end up doing is taking the dollar amount,
subtracting the TRUNCATED amount, multiplying by 100, adding 100. (In the
above case, this would yield 107...but actually 106.9999986). So I then need
to ROUND this and at last, take the RIGHT 2 characters.

There has got to be a better way to go from 1000.07 to 07. Any help would be
kindly appreciated.

Pradhan
 
D

David Biddulph

=INT(A1) for the dollars

=TEXT(100*MOD(A1,1),"00") for the cents
or =100*MOD(A1,1) and format as 00
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

split dollars and cents 8
Separating dollars and cents 5
using randombetween to arrive at dollars and cents 3
Using Text to Columns with a function 6
Changing $ format to a written format 2
Odd Numbers 5
convert figures into words on Microsft Excel 3
How to get decimal point between dollars and cents 1

Top