Extract data between several brackets within a cell

[drumroll]

Hello, I have looked at various posts and tried to use the formulas provided there but somehow I am unable to get the desired outcome. I have a long piece of text in a column which has various pieces of text within double brackets. I want to extracts all such occurrences where there is text within double brackets. The text within brackets can of of different lengths.

Column A	Desired Outcome
This is [[Text 1]] followed by [[Text 2]], followed by several chars [[Text 3]] followed by....	[[Text 1]] [[Text2]] [[Text 3]]

Thanks in advance for your help

 

[JEC]

Here a UDF

Put this behind a module. Then you can use it like

=jec(A1)

Function jec(c As String) As String
 Dim it
 With CreateObject("vbscript.regexp")
   .Global = True
   .Pattern = "[[[].*?[]]]"
   If .test(c) Then
     For Each it In .Execute(c)
       jec = Trim(jec) & " " & it
     Next
   End If
 End With
End Function

 

[Fluff]

Or with a formula, but will only work on a Windows PC.

Fluff.xlsm
	A	B
1
2	This is [[Text 1]] followed by [[Text 2]], followed by several chars [[Text 3]] followed by....	[[Text 1]] [[Text 2]] [[Text 3]]
Sheet5

Cell Formulas
Range	Formula
B2	B2	=LET(f,FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(A2,"[[","]]"),"]]","</m><m>")&"</m></k>","//m"),"[["&TEXTJOIN("]] [[",,INDEX(f,SEQUENCE(ROWS(f)/2,,2,2)))&"]]")

 

[drumroll]

Here a UDF

Put this behind a module. Then you can use it like

=jec(A1)

Function jec(c As String) As String
 Dim it
 With CreateObject("vbscript.regexp")
   .Global = True
   .Pattern = "[[[].*?[]]]"
   If .test(c) Then
     For Each it In .Execute(c)
       jec = Trim(jec) & " " & it
     Next
   End If
 End With
End Function

Wow! thanks for responding so quickly. Today I learnt something new. It worked like a charm!
Thanks again as you've saved me a lot of time

 

[drumroll]

Or with a formula, but will only work on a Windows PC.

Fluff.xlsm
	A	B
1
2	This is [[Text 1]] followed by [[Text 2]], followed by several chars [[Text 3]] followed by....	[[Text 1]] [[Text 2]] [[Text 3]]
Sheet5

Cell Formulas
Range	Formula
B2	B2	=LET(f,FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(A2,"[[","]]"),"]]","</m><m>")&"</m></k>","//m"),"[["&TEXTJOIN("]] [[",,INDEX(f,SEQUENCE(ROWS(f)/2,,2,2)))&"]]")

Thanks Fluff for taking the time. Unfortunately i cannot try this as I have a Mac. However the solution provided by Jec worked so very grateful to both of you!

 

[JEC]

You're welcome!

 

[Fluff]

Unfortunately i cannot try this as I have a Mac

Then why does your profile also show windows?

 

[drumroll]

Then why does your profile also show windows?

Sorry this must be me. I only found this forum today and created my account. I'll try to fix it.

 

[JEC]

I do have another formula based solution for you

Book1
	A	B
1
2	This is [[Text 1]] followed by [[Text 2]], followed by several chars [[Text 3]] followed by....	[[Text 1]] [[Text 2]] [[Text 3]]
Sheet1

Cell Formulas
Range	Formula
B2	B2	=LET(r,MID(A2,UNIQUE(SEARCH("[[",A2&"[[",SEQUENCE(LEN(A2)))),SEQUENCE(,20)),TEXTJOIN(" ",,IF((LEFT(r,2)="[[")*(RIGHT(r,2)="]]"),r,"")))

 

[drumroll]

I do have another formula based solution for you

Book1
	A	B
1
2	This is [[Text 1]] followed by [[Text 2]], followed by several chars [[Text 3]] followed by....	[[Text 1]] [[Text 2]] [[Text 3]]
Sheet1

Cell Formulas
Range	Formula
B2	B2	=LET(r,MID(A2,UNIQUE(SEARCH("[[",A2&"[[",SEQUENCE(LEN(A2)))),SEQUENCE(,20)),TEXTJOIN(" ",,IF((LEFT(r,2)="[[")*(RIGHT(r,2)="]]"),r,"")))

Cell Formulas
Range	Formula

Thanks for this. I think the VBA works better. Appreciate your response.