Regular expression to find negative number

[Viko]

What regular expression I can use in vba to Convert the number 123456- to -123456. I am asking this because I am trying to read txt file and need to recognize when number is negative and positive.

Any ideas?

[Bob Phillips]

Why RegEx?



Selection.Replace What:="-", Replacement:=""

[Viko]

This code does not do what I need. It just takes the "-" out and I need to put it in front of the number so excel can recognize it as a negative number

[Tommy]

Data would contain the number "123456-"
This will not work of course if there is more informationin the variable than the number.
If InStr(1, Data, "-") > 0 And InStr(1, Data, "-") <> 1 Then Data = "-" & Replace(Data, "-")

[Bob Phillips]

Here is a VBA solution



Dim rng As Range
Dim cell As Range
Dim LastRow As Long
Dim i As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "M").End(xlUp).Row
.Rows(1).Insert
.Range("M1").Value = "tmp"
Set rng = .Range("M1").Resize(LastRow + 1)
rng.AutoFilter field:=1, Criteria1:="*-"
On Error Resume Next
Set rng = rng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Nothing Is Nothing Then

rng.AutoFilter
rng.Replace "-", ""
.Range("M1").Value = 0
For Each cell In rng

cell.Value = cell.Value * -1
Next cell
End If
.Rows(1).Delete
End With