Convert dr / cr to values in tally exported file

Find the attached file with solution.

Thanks .......

Dear Vaibhav,

This Macros has helped me a lot. Thank you so much for it.

CA Priyanka Reddy

Great / Awesome

Hey @ Vaibhav Thanks for the great file. its saved my lot of time and other's also.

Regrad,

Amit Pathak

Great, I am glad it helped you.

The macro is quite smart. Thanks. Wonder why on earth does Tally export in this un-usable format ? 

Tally believes in keeping things simple.

I have simplest solution, copy desired column from excel, to notepad and paste it back to adjacent column in excel let's say j12 column .

1. Now write following in next column k12. (i am assuming its ledger) 

=right(j12, 2)     this will print all Dr and Cr in k column. 

Now copy and save the values of k column. 

2.find and replace all Dr and cr of J column with blank. 

3.   Now in L12 column, write this formula

=if(k12="Dr", j12, j12*-1)

This will solve your format problem and repeat this on other columns. 

We have a product for forensic analysis and fraud investigations, which take care of these automatically. Visit us at www.iacuityfintech.com or contact gaurav.batheja @ iacuityfintech. Com. 

Hope this helps,

Sandeep Thakur 

Replace Dr/Cr with +/- in Excel of Data Exported from Tally by 2 ways.

1) In Excel, select the amounts and then go to Ctrl + F then type _Cr (Spacebar Cr) then go to Lookin menu and select values then find all option then select all resulted lines and colour it from home menu and close Ctrl+F. You will find all Cr as coloured and you can apply formula in blank cell =Concatenate("-", Cell)

2) In Tally, Export data in ASCII Mode then that exported file will open in text format then copy all data from that in paste in Excel sheet then select all data and go to Text to Data Column menu then Delimiter then select comma menu then finish.