I have data downloaded where negative numbers are shown as 35,650- which is viewed by Excel as text. The code below moves the "-" from the right to the left thus allowing Excel to understand it as a negative which is what I need.

The problem is it get's trimmed. 35,650- shows as -35 not -35,650. What needs to change to show the whole #?

Thanks,

Kurt

Private Sub texttonegative()

Dim MemberCell As Range, DACELL

For Each MemberCell In ActiveSheet.UsedRange

If Right(MemberCell.Formula, 1) = "-" Then

MemberCell.Formula = Trim(MemberCell.Formula)

For DACELL = 1 To Len(MemberCell.Formula)

If Mid(MemberCell.Formula, DACELL, 1) = Chr(44) Then 'IF HAS COMMA

MemberCell.Formula = Left(MemberCell.Formula, DACELL - 1) _

& Right(MemberCell.Formula, Len(MemberCell.Formula) - 2)

End If

Next

MemberCell.Formula = _

Val("-" & Val(Left(MemberCell.Formula, _

Len(MemberCell.Formula) - 1))) 'Formula for moving sign

End If

Next

End Sub