Search code examples
regexwhitespacelooker-studioregexp-replacere2

Replace multiple blank spaces in string in Google Data Studio (Regex)

I'm trying to clean up my data for a Google Data Studio report by replacing multiple spaces in a string with a single space using a REGEXP_REPLACE formula, but I cannot work out how to do it. I think this is a peculiarity when working in Google Data Studio.

Here is an example...

Record with Campaign name=

"   This       is  an     example     string  with many  blank     spaces     "

I then create a Custom field in Data Studio with formula

REGEXP_REPLACE(Campaign name,'/s','')

I have tried adding in multiple combinations of:

/s+ /s* \/s*\
s/g
[/s] /s{5}

Note: do not be confused...when looking at the output results in Data Studio, Google strips out the extra spaces for presentation purposes - but they are still there and mess up sorting, etc.

Solution

  • Use either of the Calculated Fields below, which incorporate the TRIM function to ensure that any potential trailing spaces are removed; the first REGEXP_REPLACE Calculated Field uses a Raw Literal (indicated by the R before the RegEx section), requiring a single \ while the second Calculated Field without the Raw Literal requires \\ for special RegEx characters and as the escape sequence:

    1) RegEx WITH Raw Literal

    TRIM(REGEXP_REPLACE(Campaign name, R"\s+", " "))

    2) RegEx WITHOUT Raw Literal

    TRIM(REGEXP_REPLACE(Campaign name, "\\s+", " "))

    Editable Google Data Studio Report (Embedded Google Sheets Data Source) and a GIF to elaborate:

    enter image description here