Extracting data from within multiple square brackets

dokouk

New Member
Joined
Oct 10, 2016
Messages
10
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"
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,203
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
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

New Member
Joined
Oct 10, 2016
Messages
10
Thanks Fluff. Unfortunately, I'm a novice Excel user so wondering if there's an alternative using a formula?



How about
Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,203
Office Version
  1. 365
Platform
  1. Windows
If it's possible to do it with a formula, then I'm afraid I don't know how.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,968
Office Version
  1. 365
Platform
  1. Windows

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

New Member
Joined
Oct 10, 2016
Messages
10
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 :(
 

bosco_yip

Well-known Member
Joined
Dec 2, 2002
Messages
1,938
Office Version
  1. 2019
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,203
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
Have a good weekend yourself. Certainly looks like we've got the right weather
 

Watch MrExcel Video

Forum statistics

Threads
1,123,135
Messages
5,599,917
Members
414,348
Latest member
KloppyM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top