I need to use VBA to discard part of this string and then convert it to a number not dates

[zeekiehafa]

Ok so, I have a list of data. Some of them look like this "4-1/2 (114)" but some look like this
"4 (114)". I want to discard the entire item in brackets. The problem I am having however is that when I write a code to automatically remove the brackets and their contents, all of the items that look like this "4-1/2 (114)" get converted to dates. I want them to be converted to numbers. For example, "4-1/2 (114)" should end up being the number 4.5 and "4 (114)" should end up being the number 4.

Help? Please?

Here's the situation

And here's the code I've been trying to use, that converts them to dates sometimes.

Dim cell As Range
Dim v As Variant
Dim c As Integer
Do While ActiveCell.Value <> ""
    c = 0
Do While ActiveCell.Value <> ""
    On Error Resume Next
    For Each cell In Intersect(Selection.Cells, ActiveSheet.UsedRange)
        If VarType(cell.Value) = vbString Then
            v = Evaluate(Replace(cell.Value, "-", " "))
            If VarType(v) = vbDouble Then cell.Value = v
        End If
    Next cell
    ActiveCell.Value = Left(ActiveCell, InStr(1, ActiveCell.Value, "(") - 2)
    ActiveCell.Offset(0, 1).Select
    c = c + 1
Loop
  ActiveCell.Offset(1, -c).Select
Loop

 

[mikerickson]

If "4-1/2 (114)" is in A1

=SUBSTITUTE(LEFT(A1,FIND(" ",A1&" ")),"-"," ")+0 will return 4.5
and
=TRIM(MID(A1,FIND(" ",A1&" "),255)) will return "(114)"

 

[lancerj017]

try

Sub converter()
Dim cell As Range
Dim v As String
Dim t As String
Selection.NumberFormat = "# ?/?"
    For Each cell In Intersect(Selection.Cells, ActiveSheet.UsedRange)
        If InStr(cell.Value, "(") <> 0 Then
            t = Left(cell.Value, Len(cell.Value) - 5)
            v = Replace(Trim(t), "-", " ")
            cell.Value = v
        End If
    Next cell
Selection.NumberFormat = "General"
End Sub

 

[zeekiehafa]

Ok, using Lancer's code on row 6 this is what happened.

the 3 values on the right have not been converted to numbers. Do you have a more dynamic solution lancer?

Thanks,
-zeek

 

[lancerj017]

try:

Sub converter()
Dim cell As Range
Dim v As String
Dim t As String
    For Each cell In Intersect(Selection.Cells, ActiveSheet.UsedRange)
        With Intersect(Selection.Cells, ActiveSheet.UsedRange)
            .NumberFormat = "# ?/?"
        End With
        If InStr(cell.Value, "(") <> 0 Then
            t = Left(cell.Value, Len(cell.Value) - 5)
            v = Replace(Trim(t), "-", " ")
            cell.Value = Application.WorksheetFunction.Text(v, "0.00")
        End If
    Next cell
    With Intersect(Selection.Cells, ActiveSheet.UsedRange)
        .NumberFormat = "@"
    End With
End Sub

 

[Rick Rothstein]

After selecting the cells you want to convert, does this macro do what you want...

Sub MakeNumbers()
  Dim Cell As Range
  On Error Resume Next
  For Each Cell In Selection
    Cell.Value = Evaluate(Replace(Replace(Left(Cell.Value, InStr(Cell.Value, "(") - 1), vbLf, ""), "-", "+"))
  Next
  On Error GoTo 0
End Sub

 

[lancerj017]

Try:

Sub converter()
Dim cell As Range
Dim v As String
Dim t As String
On Error Resume Next
    For Each cell In Intersect(Selection.Cells, ActiveSheet.UsedRange)
        With Intersect(Selection.Cells, ActiveSheet.UsedRange)
            .NumberFormat = "# ?/?"
        End With
        If InStr(cell.Value, "(") <> 0 Then
            t = Left(cell.Value, Len(cell.Value) - 5)
            v = Replace(Trim(t), "-", " ")
            cell.Value = Application.WorksheetFunction.Text(v, "0.00")
        End If
    Next cell
    With Intersect(Selection.Cells, ActiveSheet.UsedRange)
        .NumberFormat = "@"
    End With
On Error GoTo 0
End Sub