• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to convert Cr amount as negative

Prodip Das

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

Attachments

Luke M

Excel Ninja
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)
Code:
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

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

Excel Ninja
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.
upload_2015-12-9_10-34-31.png
 

Attachments

Sridevi Tolety

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

Amit_KK

New Member
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.
 
Last edited by a moderator:
Top