CODEWITHSUNDEEP
excelvba
user3899966
user3899966

Reputation: 63

How can I remove text after '*' or '-' characters using VBA in Excel?

I have written the following code to remove text after '*' or '-' characters in one of the rows using VBA in Excel but it's giving an error.

Sub Removetext()

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

End Sub

How can I remove the text after these characters?

Upvotes: 5

Views: 26210

Answers (1)

Scott Craner
Scott Craner

Reputation: 152585

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.

Upvotes: 6

Related Questions