How to covert Cr amount as Negative while data exported from Tally Software

[Prodip Das]

I often need to export data from Tally Accounting Software to Excel. While exporting data from Tally the positive amount transferred as Dr and credit amount as Cr . However I need to show the Dr amount as positive and Cr amount as negative like (-) 10,000 or (10,000). I have tried to use function like IF.. RIGHT..SUBSTITUTE or IF..RIGHT..LEFT...LEN, but it doesn't work. Though the Dr or Cr followed by number is displaying in excel, however when i put the cursor in respective cell it shows only the number and no Dr or Cr appears. More over the range in auto sum considered the numbers as positive.
Please refer the file attached herewith. I would appreciate you for solving the above problem.

 

[Beamer]

Hi Prodip Das.
That is a bit tricky, but after much consulting with the Google God, I came up with this:-

1. Select Cell C5 of Trial Balance Sheet
2. Press Ctrl+F3 > New and in the Name box, type cell_format
3. In the Refers to box, enter this formula =GET.CELL(53,'Trial Balance'!$C5) and click on Close

On Sheet1 Cell C5 enter this formula and copy down: =IF(RIGHT(cell_format,2)="Cr",-('Trial Balance'!C5),'Trial Balance'!C5)

Edit: If you wish to get rid of the Cr/Dr just reformat the cells as currency.

 

[Prodip Das]

Wow..its great..thanks a lot..I was trying this solution and visit several sites. But u are great man..

 

[ndvora]

Very much Thanks, it is still working in office 365 and is amazing,solved the problem.