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]

This one is more stable than the last formula

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(u,UNIQUE(SEARCH("[[",A2&"[[",SEQUENCE(LEN(A2)))),r,IFERROR(MID(A2,u,SEQUENCE(,LEN(A2))),""),TEXTJOIN(" ",,IF((LEFT(r,2)="[[")*(RIGHT(r,2)="]]")*(ISERROR(SEARCH(" [",r))),r,"")))

 

[drumroll]

This one is more stable than the last formula

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(u,UNIQUE(SEARCH("[[",A2&"[[",SEQUENCE(LEN(A2)))),r,IFERROR(MID(A2,u,SEQUENCE(,LEN(A2))),""),TEXTJOIN(" ",,IF((LEFT(r,2)="[[")*(RIGHT(r,2)="]]")*(ISERROR(SEARCH(" [",r))),r,"")))

Genius! Yes this worked as well though it looked like Excel was working hard to get the output Thanks so much!

 

[Rick Rothstein]

For those who like to avoid calling out to a RegExp engine, here is a solution employing VBA functionality only...

Function DoubleBrackets(S As String) As String
  Dim X As Long, Arr As Variant
  Arr = Split(Replace(S, "]]", "[["), "[[")
  For X = 1 To UBound(Arr) Step 2
    DoubleBrackets = DoubleBrackets & " " & "[[" & Arr(X) & "]]"
  Next
  DoubleBrackets = Mid(DoubleBrackets, 2)
End Function

You would use this in cell like so...
=DoubleBrackets(A1)

 

[JEC]

Here a oneliner

Function jec(c As String) As String
jec = Join(Filter(Split(Replace(Replace(c, "]]", "]]^"), "[[", "^[["), "^"), "[["))
End Function

 

[Rick Rothstein]

Here a oneliner

Function jec(c As String) As String
jec = Join(Filter(Split(Replace(Replace(c, "]]", "]]^"), "[[", "^[["), "^"), "[["))
End Function

Nice use of Filter. Why didn't you post this code in your first reply?

 

[JEC]

Thanks! I usually go with regex. I didn’t think of this approach first

 

[drumroll]

i love this forum. You guys are awesome!