Userdefined Function / Formulae

[JayModi]

Dear Excel Experts-

We use SAP system and whenever I copy any data (from SAP) and paste it to excel, it paste it in following format (which is incompatible to sum):

1,468.98
29,463.83
2,281.10-
12,684.40-
64,254.04-

So this causes us manually to change it to:

1,468.98
29,463.83
-2,281.10
-12,684.40
-64,254.04

Is there a way I can write a formulae or function in VBA which will automatically change the 64,254.04- to -64,254.04 and leave the postive ones as it is? If there is something like that than I would really apprecite if you can provide the VBA code (procedure).

Thank You in advance.
Jay

 

[Tinbendr]

We use SAP system

My condolences.

Try this.

Sub ChangeSigns()
Dim A As Long
Dim ColNum As Long
'Cursor must be in column in question.
ColNum = ActiveCell.Column

With ActiveSheet
    LastRow = .Cells(.Rows.Count, ColNum).End(xlUp).Row
    For A = 1 To LastRow
        If Right(.Cells(A, ColNum).Value, 1) = "-" Then
            .Cells(A, ColNum).Value = _
                Left(.Cells(A, ColNum).Value, _
                Len(.Cells(A, ColNum).Value) - 1) * -1
        End If
    Next
End With
End Sub

My ZAP doesn't produce any negative numbers in my work, but assuming they get translated as text, this should work.

 

[JayModi]

Thank a lot David. That did the trick.

Jay

 

[shg]

You can also select the column and do Data > Text to columns, Finish.