Extract between two Brackets in Excel 2016

[Geo Jul]

Dear All,​

May I kindly request to help me with a formula to extract only?​

TO EXTRACT ONLY =0 BETWEEN BRACKETS IN EXCEL FORMULA 2016​

[ 201=1 ][ 203=0 ][ 206=0 ][ 209=0 ][ 210=5 ][ 220=0 ][ 221=0 ][ 226=0 ][ 230=0 ][ 235=3 ][ 237=0 ][ 239=0 ][ 241=4 ]​

The expected answer should be like this​

[ 203=0 ][ 206=0 ][ 209=0 ][ 220=0 ][ 221=0 ][ 226=0 ][ 230=0 ][ 237=0 ][ 239=0 ]​

Or​

[ 203=0 ]​

[ 206=0 ]​

[ 209=0 ]​

[ 220=0 ]​

[ 221=0 ]​

[ 226=0 ]​

[ 230=0 ]​

[ 237=0 ]​

[ 239=0 ]​

Many thanks​

 

[alansidman]

If you don't require the brackets then this Power Query solution should work for you

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", each ([Column1] <> "[" and [Column1] <> "][" and [Column1] <> "]")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each if Text.Contains([Column1],"0") then [Column1] else null),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([Custom] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Custom"})
in
    #"Removed Columns"

 

[Michael M]

Maybe also

Sub MM1()
Dim X As Variant, r As Long, lr As Long
X = Split(Range("A1").Value, " ][ ")
Range("A1").Resize(UBound(X) - LBound(X) + 1).Value = Application.Transpose(X)
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 1 Step -1
If Right(Range("A" & r), 1) <> "0" Then Rows(r).Delete
Next r
End Sub

 

[Rick Rothstein]

It is not clear from your post whether your existing data is a text string or individual cells of data (your second expected answer are individual cells, hence the confusion). It looks like a text string, so I'll assume that. Give this formula a try...

=CONCAT(LET(t,TEXTSPLIT("]"&A1&"[","]["),"["&FILTER(t,ISNUMBER(FIND("=0",t)),"")&"]"))

 

[Geo Jul]

It is not clear from your post whether your existing data is a text string or individual cells of data (your second expected answer are individual cells, hence the confusion). It looks like a text string, so I'll assume that. Give this formula a try...

=CONCAT(LET(t,TEXTSPLIT("]"&A1&"[","]["),"["&FILTER(t,ISNUMBER(FIND("=0",t)),"")&"]"))

thank you very much for your response and perfect solution

in our office our staff use Excel 2016 , 2010

my I kindly request you to amend your formula to be used in excel

2016 or 2010

Thank you

 

[Peter_SSs]

Are the numbers before the "=0" always 3 digits like your samples? If so, try this.

BTW, please just write any text in your post as normal text in the Reply window, not in a table like you have been doing in this thread.

23 11 27.xlsm
	A	B
1	[ 201=1 ][ 203=0 ][ 206=0 ][ 209=0 ][ 210=5 ][ 220=0 ][ 221=0 ][ 226=0 ][ 230=0 ][ 235=3 ][ 237=0 ][ 239=0 ][ 241=4 ]	[ 203=0 ]
2		[ 206=0 ]
3		[ 209=0 ]
4		[ 220=0 ]
5		[ 221=0 ]
6		[ 226=0 ]
7		[ 230=0 ]
8		[ 237=0 ]
9		[ 239=0 ]
10
11
12
Extract =0

Cell Formulas
Range	Formula
B1:B12	B1	=IFERROR(RIGHT(LEFT(A$1,FIND("#",SUBSTITUTE(A$1,"=0 ]","#",ROWS(B$1:B1)))+3),9),"")

 

[Geo Jul]

Are the numbers before the "=0" always 3 digits like your samples? If so, try this.

BTW, please just write any text in your post as normal text in the Reply window, not in a table like you have been doing in this thread.

23 11 27.xlsm
	A	B
1	[ 201=1 ][ 203=0 ][ 206=0 ][ 209=0 ][ 210=5 ][ 220=0 ][ 221=0 ][ 226=0 ][ 230=0 ][ 235=3 ][ 237=0 ][ 239=0 ][ 241=4 ]	[ 203=0 ]
2		[ 206=0 ]
3		[ 209=0 ]
4		[ 220=0 ]
5		[ 221=0 ]
6		[ 226=0 ]
7		[ 230=0 ]
8		[ 237=0 ]
9		[ 239=0 ]
10
11
12
Extract =0

Cell Formulas
Range	Formula
B1:B12	B1	=IFERROR(RIGHT(LEFT(A$1,FIND("#",SUBSTITUTE(A$1,"=0 ]","#",ROWS(B$1:B1)))+3),9),"")

Many thanks for your help, response, and perfect solution

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

If you want, here is a slightly simpler version.

23 11 27.xlsm
	A	B
1	[ 201=1 ][ 203=0 ][ 206=0 ][ 209=0 ][ 210=5 ][ 220=0 ][ 221=0 ][ 226=0 ][ 230=0 ][ 235=3 ][ 237=0 ][ 239=0 ][ 241=4 ]	[ 203=0 ]
2		[ 206=0 ]
3		[ 209=0 ]
4		[ 220=0 ]
5		[ 221=0 ]
6		[ 226=0 ]
7		[ 230=0 ]
8		[ 237=0 ]
9		[ 239=0 ]
10
11
12
Extract =0

Cell Formulas
Range	Formula
B1:B12	B1	=IFERROR(MID(A$1,FIND("#",SUBSTITUTE(A$1,"=0 ]","#",ROWS(D$1:D1)))-5,9),"")

 

[Geo Jul]

You're welcome. Thanks for the follow-up.

If you want, here is a slightly simpler version.

23 11 27.xlsm
	A	B
1	[ 201=1 ][ 203=0 ][ 206=0 ][ 209=0 ][ 210=5 ][ 220=0 ][ 221=0 ][ 226=0 ][ 230=0 ][ 235=3 ][ 237=0 ][ 239=0 ][ 241=4 ]	[ 203=0 ]
2		[ 206=0 ]
3		[ 209=0 ]
4		[ 220=0 ]
5		[ 221=0 ]
6		[ 226=0 ]
7		[ 230=0 ]
8		[ 237=0 ]
9		[ 239=0 ]
10
11
12
Extract =0

Cell Formulas
Range	Formula
B1:B12	B1	=IFERROR(MID(A$1,FIND("#",SUBSTITUTE(A$1,"=0 ]","#",ROWS(D$1:D1)))-5,9),"")

Thank you very much for your responses and follow-up perfect formula and solution

 

[Rick Rothstein]

Thank you very much for your responses and follow-up perfect formula and solution

I think you marked my post as the Answer by mistake... given your comments, it seems like @Peter_SSs' last post should be marked as the Answer.