# Userdefined Function / Formulae

#### JayModi

##### New Member
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).

Jay

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### Tinbendr

##### Well-known Member
We use SAP system
My condolences.

Try this.
Code:
``````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

##### New Member
Thank a lot David. That did the trick.

Jay

#### shg

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