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

user_16451359 0
Viewed 913

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.

re2 regexp-replace google-data-studio whitespace regex
1 Answers

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

user_128925530
Related