How to split a string that has comma or pipe delimiters using REGEXP_EXTRACT in Looker Studio?

Recently, a client of ours asked us to work on an Ads dashboard with loads of valuable data coded into the Campaign Name. As we've forgotten about the exact syntax of regex, we went on-line, but struggled to find a quick answer, so we decided to write our own article on the topic for future reference! With no further delay, we'll show you how to split a string that has comma or pipe delimiter using REGEXP_EXTRACT in Looker Studio / Data Studio.


Need help ? We offer Looker Studio Support for companies.

Looking for a connector ? Check Supermetrics.


looker studio how to split a string that has comma or pipe delimiters using regexp_extract

Use REGEXP_EXTRACT to split string with pipe delimiter

With no further delay, let's say you want to extract the fifth element of a pipe delimited string (careful here, we start counting at 0 with the function REGEXP_EXTRACT).

Nothing easier than that, just use the following formula and you're done:

  • REGEXP_EXTRACT(Campaign name, '^(?:[^\\|]*\\|){4}([^\\|]*)')


The only thing to consider here is escaping the pipe |, ie putting \\ so Looker Studio understands what we're talking about, and the number between brackets to tell which part you're looking for.



Use REGEXP_EXTRACT to split string with pipe delimiter

Very similar, but here no need to escape the commas, and it goes like this (again, to get the fifth element of your string):

  • REGEXP_EXTRACT(Campaign name, '^(?:[^,]*,){4}([^,]*)')

In this quick article, we've reviewed how to split a string that has comma or pipe delimiters using REGEXP_EXTRACT in Looker Studio.

PROBLEM SOLVED !


Previous article: Best practices to monitor your ads performances in Looker Studio

Next article: How to show two comparisons at the same time on a scorecard in Looker Studio?

Looking for something else? All our articles can be found on our blog page