Convert tally exported cr. as (-) negative in excel

It is more better to change the tally configration from configuration--Numeric Symbols

Good sir, it is working.

From gateway of tally, go to f12 (Configuration) - Numeric Symbols

Symbols to use for Debit account  > Symbol before number (prefix) : Change from blank to (+) and

Symbols to use for Debit account  > Symbol after number (suffix) : Change from '_Dr' to blank and

Symbols to use for Credit account  > Symbol before number (prefix) : Change from blank to (-) and

Symbols to use for Credit account  > Symbol after number (suffix) : Change from '_Cr' to blank.

Close tally restart and export (even in display you get +/- instead of Dr/Cr

Thanks Vishwal... for your Best one suggestions. 

Excellent Sir

Excellent Sir

Brilliant Prateek.

Such a wonderful Solution.

you kept the trust of CA degree, although I know it is not matter of degree, but I am happy that a CA knowing the exact pain area of client and resolution.

Thanks

That's the easiest and simplest way - did the trick for me. I just had to paste to word and then paste back to excel. Witty solution.

Imagine the values are in Column A. Filter column A for Cr. Go to column B and enter the formula =A1 * -1. Copy this formula and paste in the entire visible area of column B.

In column A filter for Dr. Now in column B enter the formula to get the positive figures from column A.

This way in column B we will have the converted figures. Now copy paste the values in column B and replace them with the values in column A.

IN VBA You can copy paste following code 

all credit will be converted to debit balance

Sub posneg()
 Dim myCell As Range
    Set W = Application.Selection
    'Set W = Application.InputBox("Select one range that you want to change positive to negative:", "ChangePositiveToNegative", W.Address, Type:=8)
    Set W = W.SpecialCells(xlCellTypeConstants, xlNumbers)
    
    
    For Each myCell In W
    
            
        xValue = myCell.Value
        yFormat = myCell.NumberFormat
        If xValue > 0 And yFormat = """""0.00"" Cr""" Then
            myCell.Value = xValue * -1
            myCell.NumberFormat = "General"
        End If
    Next
End Sub
 

Hi, you are able to change the F12 configuration in Tally so that it exports reports as negative or positive which can be used in calculations.

Gateway of Tally - F12 Configure - Numeric Symbols

Change Symbol for Credit amount" And "Symbol for Debit Amount" to "-" and "+" accordingly.

Mr. Prateek!!
Thanks for an easy way to separate Debit/Credit numbers..

Dear All

Can we filter Cr. first & colour it as Red & proceed further with modification as per our requirements. 

Select the entire excel sheet

Use search and replace function to search Cr and replace it with just entering the enter/ return button ie nothing

Multiply the entire sheet with -1 by using paste special function All credit balance will become as -ve numbers whereas Dr one will remain as it is .

Again use search and replace function and  find Dr and replace it with blank enter button. All debit balance will  now be positive numbers

Thats it . 

Select the entire excel sheet

Use search and replace function to search Cr and replace it with just entering the enter/ return button ie nothing

Multiply the entire sheet with -1 by using paste special function All credit balance will become as -ve numbers whereas Dr one will remain as it is .

Again use search and replace function and  find Dr and replace it with blank enter button. All debit balance will  now be positive numbers

Thats it .