Change negative sign from end of number to the beginning of the number.

[GORDON SCALES via OfficeKB.com]

I need to scan financial reports to a file so that I can then manipulate
columns of figures. I can do this using an OCR program and saving as an
Excel file.

The reports have the negative sign at the end of the number but Excel
requiires it at the begiinning.

eg: 1234.56- needs to convert to -1234.56

Is there a way that I can convert a whole column

 

[Guest]

Does this work for you:

Sub ConvertNeg()
Dim rng As Range
Dim oCell As Range

Set rng = Range("B1:B100")
For Each oCell In rng
If Right(oCell, 1) = "-" Then
oCell = Left(oCell, Len(oCell) - 1) * -1
End If
Next
Set rng = Nothing
End Sub

 

[Neil]

Gordon,

Try this, you will have to change the sheet and range to suit your data.

Sub ChangeMinus()
'changes the minus sing on the right of the number
'which is actually shown as text to the left
'so that it becomes a negative number
Dim checkRange As Range
Dim Cell As Range
Set checkRange = Sheets(2).Range("D:I")
For Each Cell In checkRange
If Right(Cell.Value, 1) = "-" And Left(Cell.Value, 1) <> "-" Then
Cell.Value = "-" &
Application.WorksheetFunction.Substitute(Cell.Value, "-", " ")
End If
Next
End Sub

Regards
Neil

 

[GORDON SCALES via OfficeKB.com]

thanks guys,