Modify a string using REGEXEXTRACT and REGEXREPLACE
Marketing Analytics in Google Sheets
Luke Pajer
Digital Marketing Specialist
Recall the digital marketing table...
REGEXREPLACE()
=REGEXREPLACE(string, regular expression, replacement string)
- Returns: original string and replacement string
- Excludes the matched part of the string
- Often used to clean up categorical data
.[u|U]sers: Replaces the word 'Users' after the strings R and Python- Enter
''to delete the word entirely=REGEXREPLACE(`string`, `regular expression`, '')
REGEXEXTRACT()
=REGEXEXTRACT(string, regular expression)
- Returns: matched portion of the original string
- Great for extracting a specific portion of different strings
- Parenthesis
()indicate the group of characters to extract
- Parenthesis
- To extract:
Python Users(.*).UsersreturnsPython
(.*).[u|U]serswould extract 'Python' and 'R' from the campaign names(.*)matches the part of the string to extract.[u|U]sersis the part of the string to match, but not extract
Using REGEXREPLACE() and REGEXEXTRACT()
To replace only a certain categorical string:
=IF(REGEXMATCH(test, regex), REGEXREPLACE(string, regex, replacement string), if False)
To extract only a certain categorical string:
=IF(REGEXMATCH(test, regex), REGEXEXTRACT(string, regex), if False)
Let's get to work!
Marketing Analytics in Google Sheets
