How to convert Cr amount as negative

[Prodip Das]

I want to convert Cr. amount as Negative which is exported from Tally accounting software to Excel. Dr. amount will remain as positive. I mean if there is an amount shows as say 100 Dr. i want to show it in excel as 100 or if there is an amount 200 Cr. i want to show it as (-) 200 or (200) as negative.

Please find attached herewith the file.

 

[Luke M]

Because the "Cr" and "Dr" aren't actually part of the cell value, but are in the cell's format, it's impossible to do this using just basic formulas.

If you install this UDF (Right-click on a sheet tab, view code to open Visual Basic Explorer [VBE]. Go to Insert - Module. Paste Code in, then close VBE)

Function CreditDebit(myCell As Range) As Double
Dim strVal As String
Dim myMult As Integer

strVal = myCell.Text

If Right(strVal, 2) = "Cr" Then
    myMult = -1
Else
    myMult = 1
End If

CreditDebit = myCell.Value * myMult

End Function

Once you have that function installed, in your workbook, you can use a formula of:
=CreditDebit(C5)

Copy to the right 1 cell and down as needed. Formula will properly convert all the values.

 

[Prodip Das]

Thanks Luke for your effort. I tried the above UDF however the result appears as #NAME?
Pls note that though the amount in the excel table appears with Dr. or Cr, however the autosum range consider all the numeric values as positive irrespective of Dr or Cr. Request you to see the attached excel table minutely.

 

[Luke M]

Sounds like you didn't put the code in the correct spot.

As I mentioned, the Dr/Cr is only in the format of the cell; that's why you're still able to sum the values. See attached for example of proper location for code.

 

[Prodip Das]

Now this is working..great..thanks a lot Luke..

 

[Sridevi Tolety]

Hi


Here's a simple solution to your problem. The end goal is to convert all credits to negative numbers:


1. Open a new blank excel workbook and "Save as" -> .csv format (Comma delimited). A .csv file does not store formulae, formatting etc.


2. From your Tally-extracted excel file, copy the sheet with the Dr-Cr formatted columns and paste into the blank .csv file


3. Save and close the .csv file. Now reopen it.


4. You'll see that all the columns in the .csv file with Dr-Cr formats now include Dr and Cr suffixed as a part of the string itself. This is now easy to work with as it can be easily read by any normal excel formula. Formulae otherwise don’t read Cr/Dr when they’re part of the format itself.


5. Copy all the data back from your .csv into a new sheet (say, Sheet 2) in your excel.


6. In sheet 3 of the excel file, apply the following formula for the cells with data (here I'm using cell H5 as an example):

=IF(RIGHT('Sheet 2'!H5,2)="Cr",-'Sheet 1'!H5,'Sheet 1'!H5)


What does this do? From the Sheet 2 that contains your un-formatted data (with Dr/Cr along with the number as string), you are first reading whether that particular cell has "Cr" as the last 2 characters of the string. If it does, you are converting that cell to negative by specifying minus of the corresponding cell from Sheet 1. If there is no "Cr" present in the string, it simply returns the original value as it is from Sheet 1.

This is a pretty simple solution as it gives you the flexibility to do whatever you want with your data once you can see Dr/Cr in the cell value.

 

[manoj_th]

Hi,

May be simple but lengthy process, any how thanks for sharing the solution.

 

[Amit_KK]

Hi,

Alternatively, you can simply apply filter >> search in Filter "Cr", >> Multiply these numbers with -1 (Apply for Visible Cells only).

Hope you get desired result.

 

[AzzA007]

HI Guys,

Would someone be able to help me convert this file? From CR and Dr into a negative positive figure so I can add up the totals for each year

 

[vletm]

AzzA007
Please reread Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

especially How to get the Best Results at Chandoo.org
This thread is ... few years old and for other member.
Open a new thread for You.