Remove text between [brackets]

Hi @ all,

I use a macro for deleting a text between two brackets in a range("a1:a6000").

Dim c As Range
Dim p1 As Double
Dim p2 As Double
For Each c In Range("a1:a60")
p1 = InStr(c, "[")
p2 = InStr(c, "]")
If p1 > 0 Then c.Value = Left(c, p1 - 1) & Right(c, Len(c) - p2 - 0)

Next c

it works fine but only for the first pair of brackets:

something like "cars[ss,s,c]" becomes "cars"

but:

auto[o,au]mo[o,au]bi[i,ay,ai]le becomes automo[o,au]bi[i,ay,ai]le


how can I modify the code to delete all the text between all the bracket pairs in a cell ?


Many thanks in advance for any help!
Gr.Benni

Hi Benni5555,

Welcome to the forum!!

I may have a solution but you just need to wrap your code in appropriate tags like this:

[CODE] Your code here [/CODE]

...before I can post it as it's a requirement of the forum.

Regards,

Robert

Hello Benni5555,

Welcome to the Forum! This will do it.

Sub RemoveDataInBrackets()

    Dim Cell    As Range
    Dim Matches As Object
    Dim RegExp  As Object
    Dim Rng     As Range
    Dim Text    As String
    Dim Wks     As Worksheet
    
        Set Wks = ActiveSheet
        
        Set Rng = Wks.Range("A1:A6000")
        
            Set RegExp = CreateObject("VBScript.RegExp")
            RegExp.Global = True
            RegExp.Pattern = "\[[^\[\]]*\]"
        
            For Each Cell In Rng
                Text = Cell
                Set Matches = RegExp.Execute(Text)
                If Matches.Count > 0 Then Text = RegExp.Replace(Text, "")
            Next Cell

End Sub

Hi Benni5555

Try this code

Sub Test()
  Dim Cel As Range
  Dim sTab() As String, sTab2() As String, Ind As Integer
  Dim sTmp As String
  '
  For Each Cel In Range("a1:a60")
    sTab = Split(Cel.Value, "]")
    For Ind = 0 To UBound(sTab)
      sTab2 = Split(sTab(Ind), "[")
      On Error Resume Next
      sTmp = sTmp & sTab2(0)
      On Error GoTo 0
    Next Ind
    Range("B" & Cel.Row).Value = sTmp: sTmp = ""
  Next Cel
End Sub

Edit : Hi Leith Ross, very good ;-)

Regards,

Thanks for adding the tags

Though you've got a solution (nice one Leith), here's my proposed solution in any case:

Option Explicit
Sub Macro2()

    'http://www.excelforum.com/excel-programming-vba-macros/1027174-remove-text-between-brackets.html

    Dim c As Range
    Dim p1 As Double
    Dim p2 As Double
    
    Application.ScreenUpdating = False

    For Each c In Range("A1:A60")
        Do Until InStr(c, "[") = 0
            p1 = InStr(c, "[")
            p2 = InStr(c, "]")
            If p1 > 0 Then c.Value = Left(c, p1 - 1) & Right(c, Len(c) - p2 - 0)
        Loop
    Next c
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert