Formula to Macro

[Chente28]

I need help to make a macro that goes through a range of cells and searches for parenthesis. When it finds a cell with one, it should delete trim all the text after the parenthesis (and the parenthesis). I did it using a formula, but how can I use this formula in vba? I hope this makes sense. I included an example worksheet so you can see the formula I used. Thanks!

[matthewspatrick]

cel = IIf(InStr(1, cel, "(") > 0, Left(cel, InStr(1, cel, "(") - 1), cel)

[Chente28]

I get a runtime error. I'll take a better look at it when I get home. Thanks man.

[Bob Phillips]

It needs to be in context



Sub RemoveParentheses()
Dim cell As Range
Dim iPos1 As Long
Dim iPos2 As Long
For Each cell In Selection
iPos1 = InStr(1, cell.Value, "(")
If iPos1 > 0 Then
iPos2 = InStr(iPos1 + 1, cell.Value, ")")
If iPos2 > 0 Then
cell.Value = Left(cell.Value, iPos1 - 1) & _
Right(cell.Value, Len(cell.Value) - iPos2)
End If
End If
Next cell
End Sub


Select the cells to modify, then run the macro.

[Chente28]

Thanks!! It works great. I had cases in which there were more than one pair of parenthesis. I just had to run the macro twice :).