Data Studio
1.8K members online now
1.8K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

RegEx Formula for extracting text from between two brackets

[ Edited ]
Follower ✭ ☆ ☆
# 1
Follower ✭ ☆ ☆

Hi,

 

I am trying to extract some text from between parentheses using REGEXP_EXTRACT function... 

 

eg. Given field "demo" with value "foo (bar)", how do I use REGEXP_EXTRACT to extract the "bar" text?

 

I've tried *many* combinations, including...

 

REGEXP_EXTRACT(demo, '\((.*)\)')

REGEXP_EXTRACT(demo, '\\((.*)\\)')

 

But always get "Could not parse formula" or "Invalid Formula"

 

This example works...

REGEXP_EXTRACT(demo, 'foo(.*)bar')

..so I'd imagine there's a specific way to escape the ( and ) chars that I can't find anywhere?!

 

An earlier solution, which claims double escaping works, no longer works: https://www.en.advertisercommunity.com/t5/Data-Studio/Creating-a-new-dimension-with-REGEXP-EXTRACT-n...

 

Thanks!

RegEx Formula for extracting text from between two brackets

Explorer ✭ ✭ ✭
# 2
Explorer ✭ ✭ ✭

Hi Paul,

 

You could try:

(?<=\()[^,\n]+(?=\))

 

Or, if the brackets persist in causing issues, use REGEX_REPLACE to swap them for commas, then use:

(?<=,)[^,\n]+(?=,)

 

Hope that helps,

Ben

RegEx Formula for extracting text from between two brackets

Follower ✭ ☆ ☆
# 3
Follower ✭ ☆ ☆

Thanks Ben,

 

Unfortunately...

 

REGEXP_EXTRACT(demo, '(?<=\()[^,\n]+(?=\))')

...doesn't work: "Could not parse formula."

 

Trying to replace the first bracket with a comma again gets me "Could not parse formula."

REGEXP_REPLACE(demo, '\(', ',')

 

I suspect that the DS validation system treats any () chars as regex, regardless of whether they're escaped or not...