VBA to grab each square bracketed string in a sentence

[tdai]

Assuming my sentences contain unknown number of square bracketed strings, like:
1. This is [dog], that is [monkey] and i have also a [goose]..
2. [one] is good but i want to have [two hundred] cars
How can I fetch only square bracketed strings using VBA code? For example:
for the first sentence i want it to return: [dog] [monkey] [goose]
for the 2nd sentence i want it to return: [one] [two hundred]

I am on MAC, so Regex is not an option unfortunately. Thank you!

 

[lrobbo314]

here's one way.

Book1
	A	B
1	1. This is [dog], that is [monkey] and i have also a [goose]..	[dog] [monkey] [goose]
2	2. [one] is good but i want to have [two hundred] cars	[one] [two hundred]
Sheet4

Cell Formulas
Range	Formula
B1:B2	B1	=Brackets(A1)

Function Brackets(s As String)
Dim b As Boolean:   b = False
Dim m As String
Dim tmp As String
Dim res As String

For i = 1 To Len(s)
    m = Mid(s, i, 1)
    If m = "[" Then b = True
    If b Then tmp = tmp & m
    If m = "]" Then
        b = False
        res = res & tmp & " "
        tmp = vbNullString
    End If
Next i

Brackets = Trim(res)
End Function

 

[lrobbo314]

Another way using Power Query if you have that available.

Book1
	A	B	C
1	Column1		Count
2	1. This is [dog], that is [monkey] and i have also a [goose]..		[dog] [monkey] [goose]
3	2. [one] is good but i want to have [two hundred] cars		[one] [two hundred]
Sheet3

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Index = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    Split = Table.ExpandListColumn(Table.TransformColumns(Index, {{"Column1", Splitter.SplitTextByDelimiter("[", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    TextBefore = Table.AddColumn(Split, "Text Before Delimiter", each Text.BeforeDelimiter([Column1], "]"), type text),
    SR = Table.SelectRows(TextBefore, each Text.Contains(_[Column1],"]")),
    Group = Table.Group(SR, {"Index"}, {{"Count", each _, type table [Column1=nullable text, Index=number, Text Before Delimiter=text]}}),
    Combine = Table.TransformColumns(Group,{{"Count", each Text.Combine(List.Transform(_[Text Before Delimiter], each "[" & _ & "]")," ")}}),
    RC = Table.RemoveColumns(Combine,{"Index"})
in
    RC

 

[kweaver]

I threw another way together:

Function betwB(text As String)
Dim firstB As Long, secondB As Long, stringBtw As String, i As Long, lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
betwB = ""
While 0 <> InStr(text, "[")
firstB = InStr(text, "[")
secondB = InStr(text, "]")
betwB = betwB & " " & Mid(text, firstB, secondB - firstB + 1)
text = Right(text, Len(text) - secondB)
Wend
End Function

 

[Rick Rothstein]

I believe this one-line function will also work...

Function Brackets(ByVal S As String) As String
  Brackets = IIf(S Like "*[*]*", "[", "") & Replace(Join(Filter(Split(Replace(S, "]", "|["), "["), "|"), "["), "|", "]")
End Function

 

[tdai]

wow - i'm just amazed by so many creative ways of getting this done! Thank you everyone for the brilliant ideas!!!

I will use Rick's one -line function in my code.