Problem with a a number ending in -

[jln via OfficeKB.com]

Ok a system that i work with outputs a neg number like this 100-. So the
problem is this, since the system does this it make that number text. What i
need is if - show up i need to some how move it to the first spot so that it
can be viewed as a number and not text.

 

[Guest]

=IF(RIGHT(A1,1)="-",-1 * VALUE(LEFT(A1,LEN(A1)-1)),A1)

 

[Guest]

Select your range and run this macro:

Sub changeCells()
Dim r As Range, cell As Range
On Error Resume Next
Set r = Intersect(Selection.SpecialCells(xlConstants, xlTextValues),
Selection)
On Error GoTo 0
If Not r Is Nothing Then
For Each cell In r
If Right(cell, 1) = "-" Then
cell.Value = CDbl(cell.Value)
End If
Next
End If
End Sub