How to display negative $ amount??

  • Thread starter Thread starter Soundar
  • Start date Start date
S

Soundar

Hello,

When ever we enter a negative $ amount, excel will automatically convert
that currency by enclosing bracket and in red.

I have an excel sheet, which contains currency as follows

$1234.00CR
$12.45CR
$12345677.00
etc.

Now it is displaying amount containing CR as is.

Is it possible to convert the above mentioned currency value which contains
CR as enclosed with brackets and in red.....

Please help me to solve this problem.

Thanks in advance,
Soundar.
 
It is just a format, so just goto menu Format>Cells>Custom and use format
of

$#,##0.00;[Red]($###0.00)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Hi Bob,

Below mentioned format is working fine, when the value is -$1234.00 etc, but
it is not converting to bracket and red when $1224.00CR

Please suggest me.

Soundar.

Bob Phillips said:
It is just a format, so just goto menu Format>Cells>Custom and use format
of

$#,##0.00;[Red]($###0.00)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



Soundar said:
Hello,

When ever we enter a negative $ amount, excel will automatically convert
that currency by enclosing bracket and in red.

I have an excel sheet, which contains currency as follows

$1234.00CR
$12.45CR
$12345677.00
etc.

Now it is displaying amount containing CR as is.

Is it possible to convert the above mentioned currency value which
contains
CR as enclosed with brackets and in red.....

Please help me to solve this problem.

Thanks in advance,
Soundar.
Click to expand...
Click to expand...
 
Sounds like those are text, you will need to re-enter them.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



Soundar said:
Hi Bob,

Below mentioned format is working fine, when the value is -$1234.00 etc,
but
it is not converting to bracket and red when $1224.00CR

Please suggest me.

Soundar.

Bob Phillips said:
It is just a format, so just goto menu Format>Cells>Custom and use
format
of

$#,##0.00;[Red]($###0.00)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Soundar said:
Hello,

When ever we enter a negative $ amount, excel will automatically
convert
that currency by enclosing bracket and in red.

I have an excel sheet, which contains currency as follows

$1234.00CR
$12.45CR
$12345677.00
etc.

Now it is displaying amount containing CR as is.

Is it possible to convert the above mentioned currency value which
contains
CR as enclosed with brackets and in red.....

Please help me to solve this problem.

Thanks in advance,
Soundar.
Click to expand...
Click to expand...
Click to expand...
 
But my report (Downloaded from mainframe) contains around 1000 line.... so
that only i am searching for any automated solution....

Soundar.

Bob Phillips said:
Sounds like those are text, you will need to re-enter them.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



Soundar said:
Hi Bob,

Below mentioned format is working fine, when the value is -$1234.00 etc,
but
it is not converting to bracket and red when $1224.00CR

Please suggest me.

Soundar.

Bob Phillips said:
It is just a format, so just goto menu Format>Cells>Custom and use
format
of

$#,##0.00;[Red]($###0.00)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Hello,

When ever we enter a negative $ amount, excel will automatically
convert
that currency by enclosing bracket and in red.

I have an excel sheet, which contains currency as follows

$1234.00CR
$12.45CR
$12345677.00
etc.

Now it is displaying amount containing CR as is.

Is it possible to convert the above mentioned currency value which
contains
CR as enclosed with brackets and in red.....

Please help me to solve this problem.

Thanks in advance,
Soundar.
Click to expand...
Click to expand...
Click to expand...
 
Can you use a helper column to transform that text to numbers?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



Soundar said:
But my report (Downloaded from mainframe) contains around 1000 line.... so
that only i am searching for any automated solution....

Soundar.

Bob Phillips said:
Sounds like those are text, you will need to re-enter them.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Soundar said:
Hi Bob,

Below mentioned format is working fine, when the value is -$1234.00
etc,
but
it is not converting to bracket and red when $1224.00CR

Please suggest me.

Soundar.

:

It is just a format, so just goto menu Format>Cells>Custom and use
format
of

$#,##0.00;[Red]($###0.00)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Hello,

When ever we enter a negative $ amount, excel will automatically
convert
that currency by enclosing bracket and in red.

I have an excel sheet, which contains currency as follows

$1234.00CR
$12.45CR
$12345677.00
etc.

Now it is displaying amount containing CR as is.

Is it possible to convert the above mentioned currency value which
contains
CR as enclosed with brackets and in red.....

Please help me to solve this problem.

Thanks in advance,
Soundar.
Click to expand...
Click to expand...
Click to expand...
 
I will try to solve this problem in Mainframe itself, because this
downloading part will be taken care by client, so I need to give final
solution only and We cant ask them to do, thats only my prob. Really thank
you so much bob for your support.

Soundar.

Bob Phillips said:
Can you use a helper column to transform that text to numbers?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



Soundar said:
But my report (Downloaded from mainframe) contains around 1000 line.... so
that only i am searching for any automated solution....

Soundar.

Bob Phillips said:
Sounds like those are text, you will need to re-enter them.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Hi Bob,

Below mentioned format is working fine, when the value is -$1234.00
etc,
but
it is not converting to bracket and red when $1224.00CR

Please suggest me.

Soundar.

:

It is just a format, so just goto menu Format>Cells>Custom and use
format
of

$#,##0.00;[Red]($###0.00)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Hello,

When ever we enter a negative $ amount, excel will automatically
convert
that currency by enclosing bracket and in red.

I have an excel sheet, which contains currency as follows

$1234.00CR
$12.45CR
$12345677.00
etc.

Now it is displaying amount containing CR as is.

Is it possible to convert the above mentioned currency value which
contains
CR as enclosed with brackets and in red.....

Please help me to solve this problem.

Thanks in advance,
Soundar.
Click to expand...
Click to expand...
Click to expand...
 
=IF(RIGHT(A2,2)="CR",-LEFT(A2,LEN(A2)-2),A2)
--
David Biddulph

Soundar said:
But my report (Downloaded from mainframe) contains around 1000 line.... so
that only i am searching for any automated solution....

Soundar.

Bob Phillips said:
Sounds like those are text, you will need to re-enter them.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Soundar said:
Hi Bob,

Below mentioned format is working fine, when the value is -$1234.00
etc,
but
it is not converting to bracket and red when $1224.00CR

Please suggest me.

Soundar.

:

It is just a format, so just goto menu Format>Cells>Custom and use
format
of

$#,##0.00;[Red]($###0.00)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Hello,

When ever we enter a negative $ amount, excel will automatically
convert
that currency by enclosing bracket and in red.

I have an excel sheet, which contains currency as follows

$1234.00CR
$12.45CR
$12345677.00
etc.

Now it is displaying amount containing CR as is.

Is it possible to convert the above mentioned currency value which
contains
CR as enclosed with brackets and in red.....

Please help me to solve this problem.

Thanks in advance,
Soundar.
Click to expand...
Click to expand...
Click to expand...
 
I think that is the proper and sensible approach. Good luck with it.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



Soundar said:
I will try to solve this problem in Mainframe itself, because this
downloading part will be taken care by client, so I need to give final
solution only and We cant ask them to do, thats only my prob. Really thank
you so much bob for your support.

Soundar.

Bob Phillips said:
Can you use a helper column to transform that text to numbers?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Soundar said:
But my report (Downloaded from mainframe) contains around 1000 line....
so
that only i am searching for any automated solution....

Soundar.

:

Sounds like those are text, you will need to re-enter them.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Hi Bob,

Below mentioned format is working fine, when the value is -$1234.00
etc,
but
it is not converting to bracket and red when $1224.00CR

Please suggest me.

Soundar.

:

It is just a format, so just goto menu Format>Cells>Custom and use
format
of

$#,##0.00;[Red]($###0.00)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in
my
addy)



Hello,

When ever we enter a negative $ amount, excel will automatically
convert
that currency by enclosing bracket and in red.

I have an excel sheet, which contains currency as follows

$1234.00CR
$12.45CR
$12345677.00
etc.

Now it is displaying amount containing CR as is.

Is it possible to convert the above mentioned currency value
which
contains
CR as enclosed with brackets and in red.....

Please help me to solve this problem.

Thanks in advance,
Soundar.
Click to expand...
Click to expand...
Click to expand...
 
Hi David,

The problem is different here, I want to convert the negative dollar amount
as follows

Eg if I am entering $1234.00CR, It have to convert it into red in enclosed
brackets (as it is converting for -$1234.00 in excel). But it is not
converting, Is it possible in excel?? My report contains around 1000 lines,
and I need to set the format as permenant...I am downloading this report from
Mainframe file.

Soundar.

David Biddulph said:
=IF(RIGHT(A2,2)="CR",-LEFT(A2,LEN(A2)-2),A2)
--
David Biddulph

Soundar said:
But my report (Downloaded from mainframe) contains around 1000 line.... so
that only i am searching for any automated solution....

Soundar.

Bob Phillips said:
Sounds like those are text, you will need to re-enter them.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Hi Bob,

Below mentioned format is working fine, when the value is -$1234.00
etc,
but
it is not converting to bracket and red when $1224.00CR

Please suggest me.

Soundar.

:

It is just a format, so just goto menu Format>Cells>Custom and use
format
of

$#,##0.00;[Red]($###0.00)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Hello,

When ever we enter a negative $ amount, excel will automatically
convert
that currency by enclosing bracket and in red.

I have an excel sheet, which contains currency as follows

$1234.00CR
$12.45CR
$12345677.00
etc.

Now it is displaying amount containing CR as is.

Is it possible to convert the above mentioned currency value which
contains
CR as enclosed with brackets and in red.....

Please help me to solve this problem.

Thanks in advance,
Soundar.
Click to expand...
Click to expand...
Click to expand...
 
You could use

=IF(RIGHT(A1,2)="CR",-SUBSTITUTE(SUBSTITUTE(A1,"CR",""),"$",""),A1)

and format as I showed you earlier.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



Soundar said:
Hi David,

The problem is different here, I want to convert the negative dollar
amount
as follows

Eg if I am entering $1234.00CR, It have to convert it into red in enclosed
brackets (as it is converting for -$1234.00 in excel). But it is not
converting, Is it possible in excel?? My report contains around 1000
lines,
and I need to set the format as permenant...I am downloading this report
from
Mainframe file.

Soundar.

David Biddulph said:
=IF(RIGHT(A2,2)="CR",-LEFT(A2,LEN(A2)-2),A2)
--
David Biddulph

Soundar said:
But my report (Downloaded from mainframe) contains around 1000 line....
so
that only i am searching for any automated solution....

Soundar.

:

Sounds like those are text, you will need to re-enter them.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Hi Bob,

Below mentioned format is working fine, when the value is -$1234.00
etc,
but
it is not converting to bracket and red when $1224.00CR

Please suggest me.

Soundar.

:

It is just a format, so just goto menu Format>Cells>Custom and use
format
of

$#,##0.00;[Red]($###0.00)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in
my
addy)



Hello,

When ever we enter a negative $ amount, excel will automatically
convert
that currency by enclosing bracket and in red.

I have an excel sheet, which contains currency as follows

$1234.00CR
$12.45CR
$12345677.00
etc.

Now it is displaying amount containing CR as is.

Is it possible to convert the above mentioned currency value
which
contains
CR as enclosed with brackets and in red.....

Please help me to solve this problem.

Thanks in advance,
Soundar.
Click to expand...
Click to expand...
Click to expand...
 
If you have $1234.00CR in A2, doesn't my formula convert it to -$1234.00 ?
[If not, what does it convert to? Did you have spaces or other characters
after the CR? What does =RIGHT(A2,2) give?]
If it does convert it, does it display differently to when you merely type
in -$1234.00 ?
--
David Biddulph

Soundar said:
Hi David,

The problem is different here, I want to convert the negative dollar
amount
as follows

Eg if I am entering $1234.00CR, It have to convert it into red in enclosed
brackets (as it is converting for -$1234.00 in excel). But it is not
converting, Is it possible in excel?? My report contains around 1000
lines,
and I need to set the format as permenant...I am downloading this report
from
Mainframe file.

Soundar.

David Biddulph said:
=IF(RIGHT(A2,2)="CR",-LEFT(A2,LEN(A2)-2),A2)
--
David Biddulph

Soundar said:
But my report (Downloaded from mainframe) contains around 1000 line....
so
that only i am searching for any automated solution....

Soundar.

:

Sounds like those are text, you will need to re-enter them.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Hi Bob,

Below mentioned format is working fine, when the value is -$1234.00
etc,
but
it is not converting to bracket and red when $1224.00CR

Please suggest me.

Soundar.

:

It is just a format, so just goto menu Format>Cells>Custom and use
format
of

$#,##0.00;[Red]($###0.00)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in
my
addy)



Hello,

When ever we enter a negative $ amount, excel will automatically
convert
that currency by enclosing bracket and in red.

I have an excel sheet, which contains currency as follows

$1234.00CR
$12.45CR
$12345677.00
etc.

Now it is displaying amount containing CR as is.

Is it possible to convert the above mentioned currency value
which
contains
CR as enclosed with brackets and in red.....

Please help me to solve this problem.

Thanks in advance,
Soundar.
Click to expand...
Click to expand...
Click to expand...
 
Thank you so much David and Bob for your valuable suggestions. Both the
method working fine. Thanks for your great help.

Regards,
Soundar.


David Biddulph said:
If you have $1234.00CR in A2, doesn't my formula convert it to -$1234.00 ?
[If not, what does it convert to? Did you have spaces or other characters
after the CR? What does =RIGHT(A2,2) give?]
If it does convert it, does it display differently to when you merely type
in -$1234.00 ?
--
David Biddulph

Soundar said:
Hi David,

The problem is different here, I want to convert the negative dollar
amount
as follows

Eg if I am entering $1234.00CR, It have to convert it into red in enclosed
brackets (as it is converting for -$1234.00 in excel). But it is not
converting, Is it possible in excel?? My report contains around 1000
lines,
and I need to set the format as permenant...I am downloading this report
from
Mainframe file.

Soundar.

David Biddulph said:
=IF(RIGHT(A2,2)="CR",-LEFT(A2,LEN(A2)-2),A2)
--
David Biddulph

But my report (Downloaded from mainframe) contains around 1000 line....
so
that only i am searching for any automated solution....

Soundar.

:

Sounds like those are text, you will need to re-enter them.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Hi Bob,

Below mentioned format is working fine, when the value is -$1234.00
etc,
but
it is not converting to bracket and red when $1224.00CR

Please suggest me.

Soundar.

:

It is just a format, so just goto menu Format>Cells>Custom and use
format
of

$#,##0.00;[Red]($###0.00)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in
my
addy)



Hello,

When ever we enter a negative $ amount, excel will automatically
convert
that currency by enclosing bracket and in red.

I have an excel sheet, which contains currency as follows

$1234.00CR
$12.45CR
$12345677.00
etc.

Now it is displaying amount containing CR as is.

Is it possible to convert the above mentioned currency value
which
contains
CR as enclosed with brackets and in red.....

Please help me to solve this problem.

Thanks in advance,
Soundar.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top