Replace with macro

[YasserKhalil]

Hello everybody
I have a range ("A1:A100") and some cells have within its text the following pattern
(anytext)
I want to replace that pattern with "" so as to get rid of this pattern

 

[YasserKhalil]

UP

 

[YasserKhalil]

Is my request clear ??
If not I can explain again!!

 

[prajul89]

Just record a macro for replacement and see what happens.

 

[Ruddles]

Don't explain, demonstrate. Show us two or three examples of the input and output. By the time you've done that, I shall have written most the code.

 

[awsumchillicrab]

Are you just trying to change cells like this:

123asdf456 to 123456
12abc34def56 to 123456
a1b2c3texttexttext45text6text to 123456

?

 

[YasserKhalil]

the problem is that the length of this pattern isn't constant
maybe 10 or 12 or 20 string length!!!

 

[YasserKhalil]

the pattern includes ( and also includes )
for example

got you man (154624g gggg)
I want to get rid of (154624g gggg)

 

[Ruddles]

But it always has the brackets around it, yes? Please show us some examples.

 

[Ruddles]

Try:-

[FONT=Fixedsys]Option Explicit[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]Public Sub ReplaceSomeText()[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  Dim ws As Worksheet
  Dim iRow As Long
  Dim iPosOpen As Long
  Dim iPosClose As Long
  
  Set ws = ThisWorkbook.Sheets(1)
  
  For iRow = 1 To 100
    iPosOpen = InStr(ws.Cells(iRow, "A"), "(")
    iPosClose = InStr(ws.Cells(iRow, "A"), ")")
    If iPosOpen > 0 And iPosClose > 0 Then
      ws.Cells(iRow, "A") = Left(ws.Cells(iRow, "A"), iPosOpen) & Mid(ws.Cells(iRow, "A"), iPosClose)
    End If
  Next iRow
  
End Sub
[/FONT]