How to convert Cr amount as a positive and Dr as a negative

[AzzA007]

Hi Guys,

Can some one help me? I have tried to apply a formula to this without much success.Can someone help me??I just want to be able to total the amount. I have used this =IF(RIGHT(B2,2)="cr",LEFT(B2,LEN(B2)-2),CONCATENATE("-",LEFT(B2,LEN(B2)-2)))

 

[jack abbey]

can you use the amount columns F & G without the characters "DR" & "CR" in them?
if you can change them and column F write each amount with a minus sign, like -14029.95
and in column G write them as standard, like 14,029.95
then its just simple
if you must add the "DR" & "CR" then its a whole work around

 

[jack abbey]

just add "Value" in your formula and Bingo!
like this:
=Value(IF(RIGHT(B2,2)="cr",LEFT(B2,LEN(B2)-2),CONCATENATE("-",LEFT(B2,LEN(B2)-2))) )

 

[AzzA007]

Im not very good with excel. been trying to make this formula work without much sucess

 

[AzzA007]

HI GUYS,

Say if i use column G,I and K as a column for the figure without DR and CR what formula would i use? it doesnt work when i do it. Would you be able to post the formula like how it would type on the spread sheet plz? sorry guys I'm a Noob when it comes to this

 

[AzzA007]

=Value(IF(RIGHT(B2,2)="cr",LEFT(B2,LEN(B2)-2),CONCATENATE("-",LEFT(B2,LEN(B2)-2))) )

This the formula i used with no success. I typed exactly the above

 

[Khalid NGO]

Hi,

Use this for F column:
=IFERROR(SUBSTITUTE(F4,"DR","")*-1,"")

and this for Column G:
=IFERROR(SUBSTITUTE(G5,"CR","")*1,"")


or single formula for both columns:
=IFERROR(IF(ISNUMBER(SEARCH("dr",F4)),SUBSTITUTE(F4,"DR","")*-1,SUBSTITUTE(F4,"CR","")*1),"")
Regards,