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.

Notify of
Inline Feedbacks
View all comments
5 months ago

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

4 months ago

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

19 days ago

Perfect! Thank you!!

James Clark
Hi! I'm James Clark and this is my website. Need help with your own website's analytics, SEO, content or ads? Contact me
Copyright © James Clark
What do you think? Leave a commentx
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram