CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
2dayholiday
2dayholiday

Reputation: 73

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

    #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,"\[(.*?)\]")

Upvotes: 1

Views: 1192

Answers (1)

NightEye
NightEye

Reputation: 11204

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).

enter image description here

Change last parameter to what order you want to get.

UPDATE:

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

Output:

output

Upvotes: 1

Related Questions