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

#### AzzA007

##### New Member
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)))

#### Attachments

• 89.3 KB Views: 6
Last edited:

#### jack abbey

##### New Member
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

##### New Member
like this:
=Value(IF(RIGHT(B2,2)="cr",LEFT(B2,LEN(B2)-2),CONCATENATE("-",LEFT(B2,LEN(B2)-2))) )

#### AzzA007

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

#### AzzA007

##### New Member
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

#### Attachments

• 12.7 KB Views: 3
Last edited:

#### AzzA007

##### New Member
=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

##### Excel Ninja
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,