How to replace PROPER formula in Looker Studio?
This week I had an interesting case while working in Looker Studio.
I have Google Analytics 4 and one of the dimensions is Event Name in format of small letters and underscore. However I need to show the events names in a nice and more easy to read way – containing capital letters and without any underscores.
How can I do it? Keep reading this article and watch a video tutorial on Youtube.
If we are talking about Google Sheets, here we have two formulas: =UPPER and =PROPER. Consequently, =UPPER formula makes all the text in the cell written in capital letters, whereas PROPER formula makes the first letters of each word written in capital letters. That’s what we need, basically!
Finding a solution
If it was only so easy… We cannot use proper formula in Looker Studio and we need to come up with some solution. Here is the one I’ve found.
CONCAT(upper(LEFT_TEXT(REGEXP_REPLACE(Event name,”_”, ” “),1)),SUBSTR(REGEXP_REPLACE(Event name,”_”, ” “),2,LENGTH( REGEXP_REPLACE(Event name,”_”, ” “))-1 ))
Replacing underscore with the space
Looks quite difficult, doesn’t it? Let me explain what is going on starting with the formula highlighted with yellow color – REGEXP_REPLACE(Event name,”_”, ” “)It is one of the regular expressions everyone who works with Google Sheets or Looker Studio needs to know. This formula helps us change in the Event name the underscore with the space. Very easy one.
Writing text with the capital letters
The next formula we are going to observe is upper(REGEXP_REPLACE(Event name,”_”, ” “)) – derived from the formula in the first row. This formula capitalizes all the letters in the cell like the same formula in Google Sheets. But the word written only in capital letters is even much more difficult to read. Hence I transformed this formula into
upper(LEFT_TEXT(REGEXP_REPLACE(Event name,”_”, ” “),1)
This formula needs to have a source expression and the length. Our source expression is this Event name but without the underscore. The length means a number of letters we want to take out of our field and we want to take only one.
Event name with the first capital letter
We already have the first letter of the text in each field capitalized but now we need to add the rest of the text. The next formula we need to reach our goal is
SUBSTR(REGEXP_REPLACE(Event name,”_”, ” “),2,LENGTH( REGEXP_REPLACE(Event name,”_”, ” “))-1 ))
Let’s start with the Substring formula. It returns a substring from the specific field of expression. This formula has 3 arguments – regular expression formula, number two that means that the word needs to start with the second letter (the first one is capital, we already have it) and the third argument has to be the length. Considering the fact that each row has a different quantity of letters I’ve used the formula LENGTH and wrote -1 in regular expression to have the full text but without the first letter.
The last step is just to join all these formulas into one. To do this, I use the concat formula that I’ve written above! This simple action of capitalizing the first letters took us so much time and 6 formulas to do it!
Hope you like this article about proper formula in Looker Studio! If you know any other ways to do it – share in the comments and don’t forget to subscribe for the newsletter!