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

 

[Sandeep Warrier]

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

Yup... realised that after posting

 

[maulikshah279]

Excel 2010

	A	B
Value	Required Result

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]26500.00 dr[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]26500.00 dr[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]125500.00 dr[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]12000.00 dr[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]45904.40 dr[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6000.00 dr[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]75618.40 dr[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]4000.00 Cr[/TD]
[TD="align: right"]-4000[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]810.00 dr[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]6000.00 dr[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]100000.00 Cr[/TD]
[TD="align: right"]-100000[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]6000.00 dr[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]198540.00 dr[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(" cr",Cell_Format)),A2*-1,"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]Cell_Format[/TH]
[TD="align: left"]=GET.CELL(7,INDIRECT("rc[-1]",FALSE))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Not Working for me, Please help me on how to use it..........

Actually its working in the immediate next cell only .......... please members help on this

 

[avineet]

Not Working for me, Please help me on how to use it..........

Actually its working in the immediate next cell only .......... please members help on this

[table="width: 500, class: grid"]
[tr]
[td]828[/td]
[td]CR[/td]
[/tr]
[tr]
[td]830[/td]
[td]CR[/td]
[/tr]
[tr]
[td]1900[/td]
[td]DR[/td]
[/tr]
[/table]

And wanted result in column 3 so I wrote this -

=IF(B1="CR",(A1)(-1),A1)

It's returning positive value only. What is wrong in this? When I tried to debug, it converts (A1) with -1 as string.

 

[vicedo]

you might consider this formula as well

=IFERROR(-VALUE(SUBSTITUTE(A1,"Cr","")),"")

 

[FormR]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]828[/TD]
[TD]CR[/TD]
[/TR]
[TR]
[TD]830[/TD]
[TD]CR[/TD]
[/TR]
[TR]
[TD]1900[/TD]
[TD]DR[/TD]
[/TR]
</tbody>[/TABLE]

And wanted result in column 3 so I wrote this -

=IF(B1="CR",(A1)(-1),A1)

It's returning positive value only. What is wrong in this? When I tried to debug, it converts (A1) with -1 as string.

Hi - try like this:

Excel 2013
	A	B	C
1	828	CR	-828
2	830	CR	-830
3	1900	DR	1900
Sheet1

Cell Formulas
Range	Formula
C1	=IF(B1="CR",-A1,A1)

 

[avineet]

Hi - try like this:

Excel 2013

	A	B	C

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]828[/TD]
[TD="bgcolor: #FAFAFA"]CR[/TD]
[TD="bgcolor: #FFFF00, align: right"]-828[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]830[/TD]
[TD="bgcolor: #FAFAFA"]CR[/TD]
[TD="align: right"]-830[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1900[/TD]
[TD="bgcolor: #FAFAFA"]DR[/TD]
[TD="align: right"]1900[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C1[/TH]
[TD="align: left"]=IF(B1="CR",-A1,A1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks - it worked. The issue was from the csv file downloaded from Bank. When I retyped numbers and CR , it worked with your formula. It had space padded. Is there quick way to remove padding.

 

[avineet]

you might consider this formula as well

=IFERROR(-VALUE(SUBSTITUTE(A1,"Cr","")),"")

Thanks - it worked with your formula too. The issue was from the csv file downloaded from Bank. When I retyped numbers and CR , it worked with your formula. It had space padded. Is there quick way to remove padding.

 

[vicedo]

you mean space at the end of the data?

 

[FormR]

Is there quick way to remove padding.

You could try:

=IF(TRIM(B1)="CR",-A1,A1)

 

[vicedo]

The value function in my formula should take care of the spaces before the "CR"