How to split a string that has comma or pipe delimiters using REGEXP_EXTRACT in Looker Studio?
Can’t find the formula again? Same thing happens to us all the time, so we wrote this article to help you split strings using…
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.
Top Looker Studio connectors we love and use on a daily basis (all with free trials): PMA - Windsor - Supermetrics - Catchr - Funnel - Dataslayer. Reviews here and there.
Not sure which one to pick? Have a question? Need a pro to get a project done? Contact us on LinkedIn or by e-mail, and we’ll clear up any doubt you might have.
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 comma 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 !
Top Looker Studio connectors we love and use on a daily basis (all with free trials): PMA - Windsor - Supermetrics - Catchr - Funnel - Dataslayer. Reviews here and there.
Not sure which one to pick? Have a question? Need a pro to get a project done? Contact us on LinkedIn or by e-mail, and we’ll clear up any doubt you might have.
Communicate and browse privately. Check Proton Mail and Proton VPN
Website hosted by Tropical Server