Formatting Data

[dk5209]

Excel data dumped by Tally Accounting Software contains "Cr" and "Dr" suffixes after figures. How to format "Cr" data as Negative Nos. and "Dr" data as Positive Nos. Im attaching the following Accounts Payable Report generated by that Software

Account Payables

M/s Food For Thought Corp.

Particulars 1-Apr-2008 to 31-Mar-2009

Opening Transactions Closing

Balance Debit Credit Balance

All Klass 2090.00Cr 0.00 0.00 2090.00Cr

Amity Hatcheries Inc. 2178.00Cr 0.00 0.00 2178.00Cr

Aardvask Beverages 155308.00 Cr 0.00 0.00 155308.00 Cr

Arcade Sweets 18125.00 Cr 0.00 0.00 18125.00 Cr

Ashoka Bowls 178360.50 Cr 0.00 0.00 178360.50 Cr

Ashton Mercantile 0.00 6,000.00 0.00 6000.00 Dr

Atmospheric Cooling Solution 0.00 21,254.00 21,254.00 0.00

Ballmer & Bros 98411.00 Cr 0.00 0.00 98411.00 Cr

Burpy Productions 55974.00 Cr 0.00 0.00 55974.00 Cr

Bundles 420475.00 Cr 0.00 0.00 420475.00 Cr

Milk Centre 810076.00 Cr 2,38,834.00 6,82,776.00 1254018.00 Cr

Tea House 297485.00 Cr 29,640.00 88,711.50 356556.50 Cr

Bottling Inc. 272163.00 Cr 0.00 0.00 272163.00 Cr

Distributors of Pepsi & Aquafina 315626.00 Cr 1,41,582.00 4,40,515.96 614559.96 Cr

Data & Sons 52744.00 Cr 0.00 0.00 52744.00 Cr

Exposures 12450.00 Cr 0.00 0.00 12450.00 Cr

G. A. Enterprises 24480.00 Cr 0.00 0.00 24480.00 Cr

G. S. Traders 8640.00 Cr 0.00 0.00 8640.00 Cr

Haj Meat Shop 2459667.00 Cr 99,126.00 10,88,861.50 3449402.50 Cr

Indian Enterprises 22920.00 Cr 0.00 0.00 22920.00 Cr

Janet Glasses 0.00 0.00 30,294.00 30294.00 Cr

Paneer Centre 198776.00 Cr 0.00 0.00 198776.00 Cr

Paul & Sons 600.00 Cr 0.00 0.00 600.00 Cr

K. Dube 13870.00 Cr 0.00 0.00 13870.00 Cr

Keka Enterprise 22574.50 Cr 0.00 0.00 22574.50 Cr

L. M. Enterprise. 6210.00 Cr 0.00 0.00 6210.00 Cr

Mark Agencies 202398.82 Cr 0.00 0.00 202398.82 Cr

Manage Hazards 86591.50 Cr 0.00 0.00 86591.50 Cr

Mascot Ventures 852.50 Cr 0.00 0.00 852.50 Cr

Md. Islam 104920.00 Cr 0.00 0.00 104920.00 Cr

Tea Estates 1818996.00 Cr 1,53,819.86 5,38,169.31 2203345.45 Cr

Michael Meat 2652037.00 Cr 1,04,170.00 10,08,007.00 3555874.00 Cr

Milk Van 1388161.00 Cr 0.00 8,44,380.00 2232541.00 Cr

Mohan's 4620.00 Cr 0.00 0.00 4620.00 Cr

M.S. Enterprise 208981.00 Cr 13,750.00 42,875.00 238106.00 Cr

M/s. Kireon Dubitel 4128.00 Cr 0.00 0.00 4128.00 Cr

Kala Enterprise 50822.50 Cr 0.00 0.00 50822.50 Cr

Moon Enterprises 56240.00 Cr 0.00 0.00 56240.00 Cr

Mundial Meat Shop 204159.00 Cr 0.00 0.00 204159.00 Cr

National Industries 9567.00 Cr 0.00 0.00 9567.00 Cr

National Poultry 3528127.50 Cr 1,91,630.00 13,58,724.00 4695221.50 Cr

Natural Grains 45940.50 Cr 0.00 0.00 45940.50 Cr

New Ice Age 29942.00 Cr 0.00 0.00 29942.00 Cr

New Pancake 19228.00 Cr 0.00 9,440.00 28668.00 Cr

Nil Yeast 18205.00 Cr 0.00 0.00 18205.00 Cr

Super Bazar 180.00 Cr 0.00 0.00 180.00 Cr

Prem Enterprises 666147.00 Cr 4,74,287.00 5,83,780.00 775640.00 Cr

Pure Milk Centre 1079.00 Cr 0.00 3,346.00 4425.00 Cr

Pure Milk Emporium 64911.50 Cr 480.00 7,236.00 71667.50 Cr

Raja Milk Supply 1042858.00 Cr 0.00 1,000.00 1043858.00 Cr

Ram Millets 216533.75 Cr 0.00 0.00 216533.75 Cr

R. Panda 61084.00 Cr 0.00 0.00 61084.00 Cr

H. P.Gas 0.00 8,398.78 34,219.36 25820.58 Cr

Salmond Ray 585.00 Cr 0.00 0.00 585.00 Cr

Sett Enterprises 195758.50 Cr 32,487.00 65,082.00 228353.50 Cr

Shree Krishna Supply 4800.00 Cr 0.00 0.00 4800.00 Cr

Singh Enterprises 61674.00 Cr 0.00 0.00 61674.00 Cr

Summit Stores 1875.00 Cr 0.00 0.00 1875.00 Cr

Sundry Creditors 24000.00 Cr 0.00 0.00 24000.00 Cr

Sure Impex 22418.00 Cr 0.00 0.00 22418.00 Cr

True Pack 1211937.40 Cr 2,06,417.62 3,64,145.80 1369665.58 Cr

Vinnie Enterprises 1767790.00 Cr 2,38,668.00 7,56,360.75 2285482.75 Cr

Xcel Mutton 108585.00 Cr 0.00 0.00 108585.00 Cr

Grand Total 21335336.47 Cr 19,60,544.26 79,69,178.18 27343970.39 Cr

Pls guide me.

 

[Meedan]

Data which you have provided confusing.


Can you please attach an excel of the same.

 

[vasim]

Asuming cell A1 has value as 842.00cr/any number.


=IF(RIGHT(A1,2)="cr",LEFT("-"&A1,FIND("cr",A1,1)),A1)


For formatting, selct data - copy - paste special - values, under error option select Convert to number


format cells as custom #,##0:[red]-#,##0


Hope this works........