How to i remove a text after '*' or '-' character using VBA in excel?

Question

I have written following code to remove text after '*' or '-' character in one of the row using VBA in excel but its giving error. Can anyone help?

Sub Removetext()

For each c In Range("A1:ZZ1")
    c.Value = Left(c.Value, InStr(c.Value, "-") - 1)
Next C

End Sub

Answer 1

As it has been said in the comments of @JNevill and @fidnwindow, you need to test whether the object of your search is found or not:

Sub Removetext()

For Each c In Range("A1:ZZ1")
    If InStr(c.Value, "-") > 0 Then
        c.Value = Left(c.Value, InStr(c.Value, "-") - 1)
    End If
        If InStr(c.Value, "*") > 0 Then
        c.Value = Left(c.Value, InStr(c.Value, "*") - 1)
    End If
Next c

End Sub

The issue is that when InStr does not find the criteria it returns 0. So now you are looking for the Left -1 characters which will throw and error.