CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Smedegaard
Smedegaard

Reputation: 21

Multiple REGEXEXTRACT in Google Sheets formula

I have a line of text in Google sheets which contains several kinds of brands.

Text in cell B2: [brand]weber[brand] [category]spring[category] [brand]bosch[brand]

Result in column D2 must be: weber bosch

I'm trying to use this formula: =REGEXEXTRACT(B2; "[brand](.*?)[brand]")

But it finds only the first [brand]....[brand] is there anyone who can help with how I find all [brand]....[brand] in a text in a text and write the text in between [brand]....[brand]

Any help appreciated! Thanks :)

Upvotes: 0

Views: 568

Answers (1)

JPV
JPV

Reputation: 27242

Assuming there are spaces between the different tags (as in your example) you can try in B2

=textjoin(", ", 1, ArrayFormula(iferror(regexextract(split(regexreplace(A2, "\](\s)\[","]_["), "_"), "\[brand\](.*?)\[brand\]"))))

If not, and the number of brands is fixed (eg. 2 brands) you can try

=regexextract(A2, rept(".*\[brand\](.*?)\[brand\]", 2))

If the number of brands present in the string is variable, you can calculate the number of brands present and use that as the second parameter in the rept() function.

=regexextract(A2, rept(".*\[brand\](.*?)\[brand\]", (len(A2)-len(substitute(A2, "[brand]",)))/(2*LEN("[brand]"))))

If you need the output to be in a single cell (as in the first formula) you can wrap join(", ", ...) around the last two formulas.

Hope that helps?

Upvotes: 1

Related Questions