VBA Code to remove a string of text from cells

[Hovecat]

I want to develop a VBA script to remove any text in a cell starting from "(". The following shows a before and after image of what I'm trying to explain. Hoping someone can help.

ROMANTIC WARRIOR (IRE)	ROMANTIC WARRIOR
ZAAKI (GB)	ZAAKI
MR BRIGHTSIDE (NZ)	MR BRIGHTSIDE
ALLIGATOR BLOOD	ALLIGATOR BLOOD
GOLD TRIP (FR)	GOLD TRIP
MY OBERON (IRE)	MY OBERON
PINSTRIPED	PINSTRIPED
FANGIRL	FANGIRL
DUAIS	DUAIS
VICTORIA ROAD (IRE)	VICTORIA ROAD
MILITARIZE (NZ)	MILITARIZE

 

[Michael M]

Do you really need VBA ??
A formula solution

=IF(ISERROR(LEFT(A1,FIND(" (",A1)-1)),A1,LEFT(A1,FIND(" (",A1)-1))

 

[Hovecat]

Do you really need VBA ??
A formula solution

=IF(ISERROR(LEFT(A1,FIND(" (",A1)-1)),A1,LEFT(A1,FIND(" (",A1)-1))

Thanks Michael. Your formula works and I'm happy to use it. But I want to be able to execute it on a regular basis with different data sets. I thought it would be better to create a macro.

 

[Michael M]

Try

Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = 2 To lr
    If InStr(Range("A" & r).Value, " (") Then
    Range("B" & r).Value = Left(Range("A" & r).Value, InStr(Range("A" & r).Value, " (") - 1)
    Else:
     Range("B" & r).Value = Range("A" & r).Value
    End If
Next r
End Sub

 

[Hovecat]

Try

Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = 2 To lr
    If InStr(Range("A" & r).Value, " (") Then
    Range("B" & r).Value = Left(Range("A" & r).Value, InStr(Range("A" & r).Value, " (") - 1)
    Else:
     Range("B" & r).Value = Range("A" & r).Value
    End If
Next r
End Sub

That's brilliant! Thanks so much.

 

[Rick Rothstein]

If your opening parenthesis, when present, is always preceded by a space and located at the end of the text (as in your examples), then this one-liner macro would work...

Sub RemoveFromSpaceParenToEnd()
  Columns("A").Replace " (*", ""
End Sub

 

[Hovecat]

If your opening parenthesis, when present, is always preceded by a space and located at the end of the text (as in your examples), then this one-liner macro would work...

Sub RemoveFromSpaceParenToEnd()
  Columns("A").Replace " (*", ""
End Sub

Yes that's work well too. Thanks Rick.