REGEXREPLACE Function in Google Sheets
The REGEXREPLACE is one of the three regex functions in Google Sheet along with REGEXEXTRACT and REGEXMATCH.
It’s used to replace a part of a text string with a different text string using regular expressions.
Regular expressions are complex search patterns or character sequences that allow you to find certain patterns in a string.
Once they have been found, they can be replaced with any other text with the REGEXREPLACE function.
What is REGEXREPLACE?
The syntax of the function is:
=REGEXREPLACE(text, regular_expression, replacement)
The arguments of the function are:
- text is the original source string, a part of which will be replaced.
- regular_expression defines what you’re looking for within the text.
- replacement is the replacing text which will be inserted into the original text.
When to use REGEXREPLACE for marketing stuff?
- Updating title tags and meta descriptions.
- Updating URLs ( to prepare for a migration).
How To Use The REGEXREPLACE Function in Google Sheets
Let’s illustrate how this function works.
Suppose you have a list of article titles with different years in them and your goal is to replace these years with 2020.
To do this, you need to write a regular expression that matches any numbers. After that, the REGEXREPLACE function will look for this regex and replace the found instances with the replacement text you define, like “2020”.
The function looks like this:
=REGEXREPLACE(A2,"[0-9]+","2020")
The regular expression that looks for any number is [0-9]+. This is what the function searches for and once it has found it, it will be replaced by the value that is defined as the third argument.
You can see that the fifth title didn’t have any number. In this case, the REGEXREPLACE function simply ignores it and doesn’t replace anything.
The REGEXREPLACE function is also helpful when you want to fill templates.
For example, you have a template that is: Email address: <email>. You want to replace this <email> part with the actual email addresses that the users enter.
The function looks like this:
=REGEXREPLACE($A$2, "<([A-Za-z]+)\>", A5)
The arguments of the function are:
- The text argument is the template text, that should remain the same for every REGEXREPLACE formula. Normally, the functions change their cell references when they are copied down an entire column. Thus, its cell reference needs to be locked and used as an absolute reference. The “$” dollar signs convert the cell reference to an absolute reference that will not change while applying the function down the column.
- Unlike in the previous example, the replacement argument is a dynamic (always changing) value here and its provided as a cell reference. Without using the “$” dollar signs, they will still be adjusted when you copy the formula down the entire column.
- The regular_expression here looks for the “<>” symbols with some word inside. It’s expressed with the [A-Za-z]+ character sequence. You can write the exact word (“email”) here, but this above solution would work for any other words too.
Summary
The REGEXREPLACE function allows you to replace any part of the texts based on advanced search criteria.
It takes some practice to understand regular expressions, but once you have mastered it, you can combine it with any other function of Google Sheets.