Using Text to Columns with a function
 

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?

Using Text to Columns with a function
 

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

On Oct 10, 3:31 pm, Arlene wrote:
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?

Using Text to Columns with a function
 

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" wrote:

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

On Oct 10, 3:31 pm, Arlene wrote:
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?

Using Text to Columns with a function
 

Ok, use this:

=(A1-INT(A1))*100

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

Hope this helps.

Pete

On Oct 10, 4:38 pm, Arlene wrote:
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" wrote:
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

On Oct 10, 3:31 pm, Arlene wrote:
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?- Hide quoted text -

- Show quoted text -

Using Text to Columns with a function
 

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

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

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

On Oct 10, 3:31 pm, Arlene wrote:
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?

Using Text to Columns with a function
 

Works perfect! Thanks for your help!!

- Arlene

"Pete_UK" wrote:

Ok, use this:

=(A1-INT(A1))*100

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

Hope this helps.

Pete

On Oct 10, 4:38 pm, Arlene wrote:
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" wrote:
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

On Oct 10, 3:31 pm, Arlene wrote:
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?- Hide quoted text -

- Show quoted text -

Using Text to Columns with a function
 

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

Pete

On Oct 10, 5:20 pm, Arlene wrote:
Works perfect! Thanks for your help!!

- Arlene



"Pete_UK" wrote:
Ok, use this:

=(A1-INT(A1))*100

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

Hope this helps.

Pete

On Oct 10, 4:38 pm, Arlene wrote:
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" wrote:
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

On Oct 10, 3:31 pm, Arlene wrote:
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?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -