How to nest two (or more) REPLACE functions in one Google Data Studio formula

11 October 2020

Recently I was working with a data source in Google Data Studio that needed a bit of tidying up. It was actually Google Analytics data, and the Event Action contained the html entities & and ' which needed to be replaced with the & and ' characters respectively.

I knew how to replace either one of those values using a calculated field:

  1. In Google Data Studio, go to Resource > Manage added data sources
  2. Click the EDIT action alongside the relevant data source
  3. Click ADD A FIELD
  4. Give my new field a name
  5. Use the REPLACE function in the formula to do something like this:

    REPLACE(Event Action,"&","&")

REPLACE in Data Studio

  1. Click Save

But how should I tackle the second replace? Well, one approach would be to create a second calculated field that uses my first calculated field as the input:

REPLACE(My Example Field,"&#039","'")

Second REPLACE in Data Studio

That works fine for two replaces, but what if I needed to make 20? Or 50? The more replaces I need, the less feasible it becomes to create and maintain a calculated field for each step.

The good news is that it is possible to nest REPLACE functions (and other functions) in Google Data Studio. So instead of the two-step process above, I could just use the following formula:

REPLACE(REPLACE(Event Action,"&","&"),"'","'")

Nested function in Data Studio

So you can see that within the one formula, the output of the & REPLACE then becomes the input for the ' REPLACE.

This seems pretty straightforward, but I couldn't find it documented anywhere on Data Studio Help so I thought I would share.

Subscribe
Notify of
guest
10 Comments
Oldest
Newest
Inline Feedbacks
View all comments
Gonzo
Gonzo
1 year ago

Brilliant! Thanks James, you saved my day 🙂

Gonzo
Gonzo
1 year ago

Brilliant!
I used it to nest 6 levels and it works flawlessly.
Thanks James, you saved my day 🙂
Gonzo

Alex
Alex
1 year ago

Best Tip ever 🙂
Didn't get it in a rush. Your formula makes so much sense!
Alex

E.E
E.E
1 year ago

Perfect! Thank you!!

Bob
Bob
11 months ago

This is unreal, thank you so much!

Tijs Van Autreve
Tijs Van Autreve
11 months ago

Perfect, thanks a lot!

Siobhan Howard
Siobhan Howard
2 months ago

Is it possible to nest these two expressions together? I would like to remove all the hypens then capitalize the first letter of each word.

REPLACE(Campaign, "-", " " )

CONCAT(UPPER(REGEXP_EXTRACT(Campaign, "(^.)")) , REGEXP_REPLACE(Campaign, "^.", ""))

Mike
1 month ago

James, thank you! You're awesome!

Jer
Jer
4 days ago

Does anyone have any idea how I could do this?

SUBSTR(Lower(Full page URL, 21, 100))

I get this error

Incorrect number of arguments; expected three.

Also, while I'm on it. Can anyone help me with ability of renaming Page titles?

Right now I've edited the data source and added 25 replace items in a caluclated, but that seems to be the limit.

James Clark
Hi! I'm James Clark and this is my website. I publish all my guides completely free, so if you find them useful then please:
Copyright © James Clark
10
0
What do you think? Leave a commentx
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram