How to extract data between square bracket [ ], but there are two square brackets [ ]-CodePudding

    #Today: I have a pair [sneakers] in [blue]

As per the data above, how should i extract the keywords blue in the 2nd square bracket ? I try to use formula below, but it only extracts the words in the first [ ] sneakers instead of blue.

=REGEXEXTRACT(B2,"\[(.*?)\]")

CodePudding user response：

Formula:

=INDEX(SPLIT(REGEXEXTRACT(REGEXREPLACE(B2, "\][^\[\]]*\[", ","), "\[(.*)\]"), ","),,2)

Formula above replaces instances of ]<any non square bracket>[ to , then use the result and fetch the string inside the square bracket [sneakers,blue,test] then split using , and get the nth column.

To see how the formula works, separate it into 4 parts (per function).

Change last parameter to what order you want to get.