How to use REGEXEXTRACT to remove spesific text between multiple brackets on Google Sheet-CodePudding

I have to extract campaign names from this text:

[Ads] | [Bing] | [Leaderboard] | UCL MATCH | 29 September - 31 Desember 2019

Ideally, I only want to extract UCL MATCH and remove all the others, how do I do this with regex? Or is there some other way to do it on Google Sheets?

I only managed to do this:

=REGEXEXTRACT(K8,"\[(.*)\]\ | \ w \|\[(. )\]\|")

which resulted in Ads] | [Bing] | [Leaderboard.

CodePudding user response：

Please read my comment to your original post.

However, assuming that your answer to all questions in that comment is YES, this should work:

=TRIM(REGEXEXTRACT(K8,"([^\|] )\|[^\|] $"))

CodePudding user response：

You can extract the 4th pipe-separated item with

=REGEXEXTRACT(K8,"^(?:[^|]*\|){3}\s*([^|]*[^|\s])")

See the regex demo. Details:

^ - start of string
(?:[^|]*\|){3} - three sequences of zero or more chars other than | and then a | char
\s* - zero or more whitespaces
([^|]*[^|\s]) - Group 1 (the actual return value): zero or more chars other than | and then a char other than whitespace and | char.