Convert "CR" Into Negative Numbers

[hardeep.kanwar]

Dear All,

I have Account Data with "DR" & "CR" Format

Excel Workbook
	A
1	Balance
2	30.00 Cr
3	50.00 Cr
4	3500.00 Cr
5	50.00 Cr
6	3300.00 Cr
7	3333.00 Cr
8	0.80 Dr
9	70.00 Dr
10	3500.00 Dr
11	3500.00 Dr
12	3312.00 Dr
13	60.00 Cr
14	90.00 Cr
15	3200.00 Cr
16	84.00 Dr
17	15.00 Cr
18	3150.00 Cr
Sheet1

What i Need to Convert "CR" Numbers Into Negative

Excel Workbook
	A	B
1	Balance	Expected Result
2	30.00 Cr	(300)
3	50.00 Cr	(500)
4	3500.00 Cr	(3500)
5	50.00 Cr	(50)
6	3300.00 Cr	(3300)
7	3333.00 Cr	(3300)
8	0.80 Dr
9	70.00 Dr
10	3500.00 Dr
11	3500.00 Dr
12	3312.00 Dr
13	60.00 Cr	(600)
14	90.00 Cr	(90)
15	3200.00 Cr	(3200)
16	84.00 Dr
17	15.00 Cr	(15)
18	3150.00 Cr	(3150)
Sheet1

Is it Possible with Formula


Thanks In Advance

 

[hardeep.kanwar]

Hi

The data in your last table does not have any CR. Also, SUBSTITUTE is case sensitive.

Try

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="10px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Balance</td><td style=";">Formula</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">30 CR</td><td style="text-align: right;;">-30</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">50 DR</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">3500 DR</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">50 DR</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">3300 DR</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">3333 CR</td><td style="text-align: right;;">-3333</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">1 DR</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">70 DR</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">3500 DR</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">3500 DR</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">3312 DR</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">60 DR</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">90 CR</td><td style="text-align: right;;">-90</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">3200 DR</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">84 DR</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">15 DR</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";">3150 DR</td><td style=";"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B2</th><td style="text-align:left">=IF(<font color="Blue">RIGHT(<font color="Red">A2,2</font>)="Cr",SUBSTITUTE(<font color="Red">LOWER(<font color="Green">A2</font>)," cr",""</font>)*-1,""</font>)</td></tr></tbody></table></td></tr></table><br />

No, Still Not Working

[https://spreadsheets.google.com/ccc?key=0AgAzPQzqieBPdFpoY0RZbzA5czB6QTFBSHJmTHJLZmc&hl=en&pli=1#gid=0

I have Attached the Link,

I want to Convert Yellow colored Column

 

[bosco_yip]

E12:

=TEXT(SUM(A12*B12,C12,D12),ʺ$##0.00;($##0.00)ʺ)

Regards

 

[hardeep.kanwar]

E12:

=TEXT(SUM(A12*B12,C12,D12),ʺ$##0.00;($##0.00)ʺ)

Regards

Dear Bosco

This is not My Requirement

 

[JB_Scotland]

I assume your data column (A) is just numbers but formatted 0.00" CR";0.00" DR" as a custom format.

If that is the case all you should need is your output column formatted as 0.00;[Red](0.00) and the formula =IF(A1<0,A1*-1,"")

 

[hardeep.kanwar]

I assume your data column (A) is just numbers but formatted 0.00" CR";0.00" DR" as a custom format.

If that is the case all you should need is your output column formatted as 0.00;[Red](0.00) and the formula =IF(A1<0,A1*-1,"")

Yes, this is the Situation, but its not working

Excel Workbook
	B	C
5	Balance	Format As 0.00;[Red](0.00)
6	3500.00 Cr
7	99270.00 Dr
8	99270.00 Dr
9	500.00 Cr
10
11	10000.00 Cr
12	15309.00 Dr
13	37618.40 Dr
14	28090.00 Dr
15	27940.00 Dr
Sundry Debtors

 

[gsbelbin]

You say that you are using two custom formats with either Cr or Dr on the end. How do you distinguish between the two types of values? What does the data look like without the formatting?

 

[JB_Scotland]

When you say it is not working, what result do you get in column C with my last formula?

 

[JB_Scotland]

I've looked at your spreadsheet online at Google Docs. It seems that your data column B is numbers but without + or - value. If I change the format of the column to Normal both the CR and DR numbers are shown as positive (+).

On Google Docs I can't see the actual custom formatting of the column B, is it a Conditional Format based on the value in some other column?

 

[hardeep.kanwar]

I've looked at your spreadsheet online at Google Docs. It seems that your data column B is numbers but without + or - value. If I change the format of the column to Normal both the CR and DR numbers are shown as positive (+).

On Google Docs I can't see the actual custom formatting of the column B, is it a Conditional Format based on the value in some other column?

Yes, When i change the Format of the column to Normal both CR and DR Numbers are Shown as Positive (+)

The Column have Normal Custom Format Like ""0.00" Cr" and ""0.00" Dr"

Two Different Type of Formating

Actually, This data is Export from Our Accounting Software

 

[Sandeep Warrier]

Hardeep,

I am unable to access the file.