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.
Looker Studio connectors we use on a daily basis and are happy with (w/ free trials): Supermetrics - Windsor - Catchr - PMA - Funnel
Not sure which one to pick? Reviews here and there. Still unsure?! Contact us with your tech stack and budget, and we’ll guide you in the right direction:
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 !
Tools we use and we like:
Todoist: for tasks management
Monday: for projects management, when tasks fall short
Tropical Server: our friends in charge of the hosting of this very website!
Tools we’d like to use (if we had the budget…):