January 2022 – LearnYouAndMe

Sometimes you may get an Excel file where Debit and Credit values are in the same column with special formatting.

Excel debit credit number formatting in same column

This is the usual case when you export files from Tally software.

Due to this type of data arrangement, one may find it difficult and time-consuming to separate debit and credit values or sum only debit or credit values or make credit values negative.

Here I am sharing solution to make things fast and easy for the above issues.

Remove Debit and Credit and make credit negative

To remove formatting and to make credit value negative, open VBA IDE by pressing ALT+F11.

In VBA IDE create one standard module by clicking on Insert tab then click on Module.

creating standard module in VBA IDE

In the newly created Standard Module paste the below VBA code.

Notice below program (sub) name is s_makeCredit_Negative

Sub s_makeCredit_Negative()
''removes special formatting and make Cr values -ve in selection
 Dim rng As Range, cel As Range
 Set rng = Selection
 For Each cel In rng
    If InStr(1, cel.NumberFormat, "Cr", vbTextCompare) <> 0 And cel.Value > 0 Then
      cel.Value = cel.Value * -1
    End If
 Next cel
 rng.NumberFormat = "General"
End Sub

Now select range then press ALT+F8 shortcut key to bring macros pane (Click on Developer tab then click Macro option).

Select “s_makeCredit_Negative” option then click on the Run button.

You can also read Various ways to run VBA (macro) in Excel

Make credit values negative in Excel