Extract text in between square brackets []

[ahmedismailfourtex]

hello,
how to extract text in between [] using PQ or any technique ?, and if i have more than one [] in the same cell what to do to extract both separately or at least the first one ?
for example [abc] and i need abc
thanks

 

[AliGW]

Re: Extract text in between square pracits []

Have you tried the text to columns feature using the square brackets as the separator?

 

[ahmedismailfourtex]

Re: Extract text in between square pracits []

yes, I am already working with this method, but I need to know if there is better way using formula!.

Have you tried the text to columns feature using the square brackets as the separator?

 

[István Hirsch]

Re: Extract text in between square pracits []

Enter the formula in B1 (if the string is in A1) and copy across as long as needed:

Excel Workbook
	A	B	C	D
1	blabla and [abc] and [defg] end	",REPT(" ",LEN($A1))),(2*COLUMNS($A:A)-1)*LEN($A1),LEN($A1)))]abc	",REPT(" ",LEN($A1))),(2*COLUMNS($A:B)-1)*LEN($A1),LEN($A1)))]defg	",REPT(" ",LEN($A1))),(2*COLUMNS($A:C)-1)*LEN($A1),LEN($A1)))]
Sheet

 

[ahmedismailfourtex]

Re: Extract text in between square pracits []

Thank you Istvan for your reply, but i need PQ or PP solution.

Enter the formula in B1 (if the string is in A1) and copy across as long as needed:

*	A	B	C	D
1	blabla and [abc] and [defg] end	abc	defg	*

<colgroup><col style="width:30px; "><col style="width:207px;"><col style="width:60px;"><col style="width:86px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B1 =TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1,"[","]"),"]",REPT(" ",LEN($A1))),(2*COLUMNS($A:A)-1)*LEN($A1),LEN($A1))) C1 =TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1,"[","]"),"]",REPT(" ",LEN($A1))),(2*COLUMNS($A:B)-1)*LEN($A1),LEN($A1))) D1 =TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1,"[","]"),"]",REPT(" ",LEN($A1))),(2*COLUMNS($A:C)-1)*LEN($A1),LEN($A1))) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[MarcelBeug]

Re: Extract text in between square pracits []

Assuming there are no nested [], like [ac], you can try the following code.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Tekst", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Start", each Text.PositionOf([Tekst],"[",Occurrence.All)),
    #"Expanded Start" = Table.ExpandListColumn(#"Added Custom", "Start"),
    #"Added to Column" = Table.TransformColumns(#"Expanded Start", {{"Start", each if _ = null then null else List.Sum({_, 1}), type number}}),
    #"Added Custom1" = Table.AddColumn(#"Added to Column", "Length", each if [Start] = null then null else Text.PositionOf(Text.Middle([Tekst],[Start],Text.Length([Tekst])),"]")),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Extracted Text", each if [Start] = null then null else Text.Range([Tekst],[Start],[Length])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Start", "Length"})
in
    #"Removed Columns"

 

[ImkeF]

Re: Extract text in between square pracits []

A different take where you end up with your findings in one row is this one:

let
    Source = #table({"Text"}, {{"blabla and [abc] and [defg] end"}, {"[sad]asd eert[re]re"}}),
    Prep = Table.AddColumn(Source, "Prep", each List.Skip(Text.Split([Text], "["), 1)),
    List = Table.AddColumn(Prep, "List", each List.Transform([Prep], each Text.Split(_, "]"){0})),
    Tbl = Table.AddColumn(List, "Table", each Table.FromRows({[List]})),
    Expand = Table.ExpandTableColumn(Tbl, "Table", Table.ColumnNames(Table.Combine(Tbl[Table]))),
    #"Removed Columns" = Table.RemoveColumns(Expand,{"Prep", "List"})
in
    #"Removed Columns"