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

 

[schielrn]

Why is row 2, 3 and 13 10 times larger than the value in column A, is that just a typo? And do you want a balnk returned if it is a debit or just its normal value?

 

[VoG]

Try in B2

=IF(RIGHT(A2,2)="Cr",LEFT(A2,LEN(A2)-3)*-1,"")

 

[hardeep.kanwar]

Try in B2

=IF(RIGHT(A2,2)="Cr",LEFT(A2,LEN(A2)-3)*-1,"")

Thanks Sir, for Quick Reply

I have to Forget the Cells are Format as 0.00" dr" and 0.00" Cr"

After Putting Your Formula All the Cells in B2 are Blanks

Excel Workbook
	A	B
1	Balance	*
2	30 Cr	*
3	50 Cr	*
4	3500 Cr	*
5	50 Cr	*
6	3300 Cr	*
7	3333 Cr	*
8	1 Cr	*
9	70 Cr	*
10	3500 Cr	*
11	3500 Cr	*
12	3312 Cr	*
13	60 Cr	*
14	90 Cr	*
15	3200 Cr	*
16	84 Cr	*
17	15 Cr	*
18	3150 Cr	*
Sheet2

 

[hardeep.kanwar]

Why is row 2, 3 and 13 10 times larger than the value in column A, is that just a typo? And do you want a balnk returned if it is a debit or just its normal value?

Yes Sir, its Just Typo

I want to Delete the CR and convert into Negative Value

For "DR", Just Normal Value

 

[VoG]

It worked for me. Try this alternative

=IF(RIGHT(A2,2)="Cr",SUBSTITUTE(A2," Cr","")*-1,"")

 

[Krishnakumar]

Hi,

Replace the custom format like

#,##0;[Red](#,##0)

HTH

 

[hardeep.kanwar]

It worked for me. Try this alternative

=IF(RIGHT(A2,2)="Cr",SUBSTITUTE(A2," Cr","")*-1,"")

I am sorry but its Not Working for me

I think the Problem in CR and DR, in Mine Data CR and DR not Reflect in the Formula Bar, Only Show the Figure, and Your Formula show ="CR"

Excel Workbook
	A	B
1	Balance	Your Formula
2	30 DR	*
3	50 DR	*
4	3500 DR	*
5	50 DR	*
6	3300 DR	*
7	3333 DR	*
8	1 DR	*
9	70 DR	*
10	3500 DR	*
11	3500 DR	*
12	3312 DR	*
13	60 DR	*
14	90 DR	*
15	3200 DR	*
16	84 DR	*
17	15 DR	*
18	3150 DR	*
Sheet1

I don't Know where I am Wrong

 

[Sandeep Warrier]

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 />

 

[Haseeb Avarakkan]

Hello Hardeep,

See the post #7, Kris's Suggestion. Select your range >> Format Cells >> Custom.

#,##0;[Red](#,##0)