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

user_17397875 0
Viewed 224

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.

google-sheets-formula google-sheets regex
3 Answers

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,"([^\|]+)\|[^\|]+$"))

user_139032830

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

try:

=INDEX(TRIM(SPLIT(A1; "|"));;4)

enter image description here

for array:

=INDEX(TRIM(SPLIT(A1:A; "|"));;4)
user_56326290
Related