Using Text to Columns with a function

[Arlene]

I just figured out how to use the Text to columns feature for separating
dollars and cents into two separate columns. It works perfectly if I type the
dollar amount into the cell but if I try to use this feature on a cell that
contains a function, Text to Column sees the function instead of the dollar
amount and does not work properly.

Basically, I am trying to add up a row of dollar amounts and the total must
be displayed with the dollars in one column and the cents in another.

Any suggestions?

 

[Pete_UK]

You can use:

=INT(A1)

in an adjacent column to get the dollars, and:

=MOD(A1,0)

in another column to get the cents. This assumes your data is in cell
A1 - adjust if necessary.

Copy both formulae down as required.

Hope this helps.

Pete

 

[Arlene]

Thanks Pete. The first formula worked perfect but for the second (MOD)
formula I had to change the divisor to 1 instead of 0.

I think I could also use =A1-INT(A1) to get the cents.

My only problem now is that I need the cents to display without the leading
0 in the dollar place and without the decimal. The the line that separates
the columns is supposed to represent the decimal.

 

[Pete_UK]

Ok, use this:

=(A1-INT(A1))*100

for the cents and copy down. Format as number with no decimal places.

Hope this helps.

Pete

 

[David Biddulph]

If you want the number of cents, try =100*MOD(A1,1)
Format to suit your preferences, perhaps number with zero decimal places.

 

[Arlene]

Works perfect! Thanks for your help!!

- Arlene

Ok, use this:

=(A1-INT(A1))*100

for the cents and copy down. Format as number with no decimal places.

Hope this helps.

Pete

 

[Pete_UK]

You're welcome, Arlene - thanks for the feedback.

Pete