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]

Hardeep,

I am unable to access the file.

Now , You can Access the File

 

[jasonb75]

What is the EXACT custom format that you are using to show Cr / Dr in the cells?

That googledocs link is still restricted access.

 

[JB_Scotland]

Hardeep has explained the problem to me via correspondence on his Google Document. I will explain here so that members may be able to come up with a solution.

His data column B is numbers all positive. Each cell is formatted with a number format. 0.00" cr" if the cell is a Credit and 0.00" dr" if the call is a debit.

He basically wants to convert these values into positive and negative values. I imagine it could be done by a macro looking at the format of each cell in the column and converting the data to positive or negative accordingly.

 

[hardeep.kanwar]

What is the EXACT custom format that you are using to show Cr / Dr in the cells?

That googledocs link is still restricted access.

""0.00" Cr" For Credit

""0.00" Dr" For Debit

Dr cells are formatted one way and the Dr cells are formatted another

 

[gsbelbin]

""0.00" Cr" For Credit

""0.00" Dr" For Debit

Dr cells are formatted one way and the Dr cells are formatted another

To reiterate my previous question how are each of these identified on the sheet?

 

[hardeep.kanwar]

To reiterate my previous question how are each of these identified on the sheet?

Its a Software Generated Sheet

When i Click on Cell and Check the Format i found 2 Types of Format

One for DR and Another one for CR

 

[Sandeep Warrier]

<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="font-weight: bold;;">Value</td><td style="font-weight: bold;;">Required Result</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">26500.00 dr</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">26500.00 dr</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">125500.00 dr</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">12000.00 dr</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">45904.40 dr</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">6000.00 dr</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">75618.40 dr</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">4000.00 Cr</td><td style="text-align: right;;">-4000</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">810.00 dr</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">6000.00 dr</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">100000.00 Cr</td><td style="text-align: right;;">-100000</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">6000.00 dr</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">198540.00 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">ISNUMBER(<font color="Red">SEARCH(<font color="Green">" cr",Cell_Format</font>)</font>),A2*-1,""</font>)</td></tr></tbody></table></td></tr></table><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>Workbook Defined Names</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">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">Cell_Format</th><td style="text-align:left">=GET.CELL(<font color="Blue">7,INDIRECT(<font color="Red">"rc[-1]",FALSE</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[jasonb75]

Maybe

Public Function CrToNegative(ByVal cr As Range)
If cr.NumberFormat = """""0.00"" Cr""" Then CrToNegative = -cr.Value
End Function

=CrToNegative(A2)

edit:-

Sandeep,

For information A2*-1 could be reduced to -A2.

 

[JB_Scotland]

Sandeep's solution seems to work well.

 

[hardeep.kanwar]

Yes, John

That's Cool Sandip

And Even Jason UDF is Also Workin

Thanks All of You for Sharing and Giving your Valuable Knowledge and Time

Thanks Again