Extracting data from within multiple square brackets

[dokouk]

Hi

How would I extract the contents of the square brackets from the following?

[A1] fjdlkfjsdlkjfalkdjfldksj [A5] ndjklafdkjda [A8] fjdlkfjsdlkjfalkdjfldksj [A2] ndjklafdkjda

Ideally, I'd like the adjacent cell to contain, "A1, A5, A8, A2"

 

[Fluff]

How about

Function dokouk(Cl As Range) As String
   Dim Sp As Variant
   Dim i As Long
   Sp = Split(Replace(Replace(Cl, "[", "|"), "]", "|"), "|")
   For i = 1 To UBound(Sp) Step 2
   dokouk = dokouk & Sp(i) & ", "
   Next i
   dokouk = Left(dokouk, Len(dokouk) - 2)
End Function

Used like =dokouk(A1)

 

[dokouk]

Thanks Fluff. Unfortunately, I'm a novice Excel user so wondering if there's an alternative using a formula?

How about

Function dokouk(Cl As Range) As String
   Dim Sp As Variant
   Dim i As Long
   Sp = Split(Replace(Replace(Cl, "[", "|"), "]", "|"), "|")
   For i = 1 To UBound(Sp) Step 2
   dokouk = dokouk & Sp(i) & ", "
   Next i
   dokouk = Left(dokouk, Len(dokouk) - 2)
End Function

Used like =dokouk(A1)

 

[Fluff]

If it's possible to do it with a formula, then I'm afraid I don't know how.

 

[Joe4]

ADVERTISEMENT

With Fluff's suggestion, you CAN use a formula, like he showed you. He just created his own function to do it.
All you have to do is paste his code into VBA, and then you can use the function in a normal formula, like you would with any other formula on your sheet.
So you do not need to know anything about VBA in order to use it (he already wrote it for you).

 

[dokouk]

Thanks Joe4, but sorry to ask but would you be able to give step by step guidance to do this. I've never used VBA to create functions

 

[Fluff]

ADVERTISEMENT

If you follow the first 4 steps here https://www.ablebits.com/office-addins-blog/2013/12/06/add-run-vba-macro-excel/ and save the workbook as macro enabled.

Then in your worksheet you enter =dokouk(A1) in exactly the same way you would a formula, where A1 is whatever cell contains your string

 

[bosco_yip]

Formula solution for Office 365 version only

In B1 CTRL+SHIFT+ENTER, (CSE) formula :

=TEXTJOIN(", ",TRUE,FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"[",""),"] ","</b>")," ","<b>")&"</a>","//b"))

Regards
Bosco

 

[dokouk]

Thanks Fluff. You're a life saver!!!! Have a lovely weekend.

 

[Fluff]

You're welcome & thanks for the feedback.
Have a good weekend yourself. Certainly looks like we've got the right weather