0

I need to take into account text across multiple cells using REGEXes to identify larger grouping in another cell.

For example, using both a Source and Medium value to determine one larger Channel Group.

I have CASE IF formula I have been using in Google Data Studio that translates/converts granular field values into bigger groupings. But how do I do exactly the same on Google Sheets?

For example:

CASE 
WHEN REGEXP_MATCH(Source, "^.(google).") AND REGEXP_MATCH(Medium, "^.(cpc).") THEN "Paid Search"

And have multiples of these to catch every possible iteration in a cell, for example:

WHEN REGEXP_MATCH(Default Channel Grouping, "^.*(Direct).*") THEN "Direct"
WHEN REGEXP_MATCH(Default Channel Grouping, "^.*(Organic).*") THEN "Organic"
WHEN REGEXP_MATCH(Default Channel Grouping, "^.*(Referral).*") THEN "Media"
WHEN REGEXP_MATCH(Default Channel Grouping, "^.*(Social).*") THEN "Social"
WHEN REGEXP_MATCH(Default Channel Grouping, "^.*(Email).*") THEN "Email"

And leave a back up:

ELSE "Other Advertising" END

Would appreciate help in figuring out how to do this.

  • Please share a copy of your sheet with source values and some manually added desired output. If you need to censor data, substitute in values as similar as possible to best demonstrate what's needed. Also, have you made a start at it? See How to Ask. – Joel Reid Apr 25 at 16:09
  • share a copy of your sheet and include an example of your desired output from some given input example – user0 Apr 25 at 20:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.