This post features 5 examples that will make your life infinitely easier as a digital marketer, showing how to use REGEXREPLACE in Google Sheets GIF by GIF.
REGEXREPLACE is an excellent way to make quick alterations to text, extract specific information and strip away unwanted characters and symbols.
Although you can use find and replace in Google Sheets to solve some of these issues, I’m personally not a fan of that approach. It’s too prone to accidentally overriding spreadsheet data and frankly, is inefficient. And your time is far too valuable for inefficiencies!
Importantly, you can also apply the same syntax to the super useful REGEXMATCH and REGEXEXTRACT functions, so it’s well worth spending some time honing your REGEX Google Sheet skills.
I’ll be adding new REGEXREPLACE examples to this post whenever I find handy examples, so be sure to check in!
REGEXREPLACE examples
The syntax principles you need to know
Before we make our way through the example, I want to quickly show the REGEXREPLACE syntax you’ll always need to use in Google Sheets.
It’ll help you better understand how REGEXREPLACE functions work in Google Sheets and the examples we’ll walk through, so bear with me for a moment.
The syntax you’ll always need to use when writing a REGEXREPLACE formula:
REGEXREPLACE(text, regular_expression, replacement)
- =REGEXREPLACE is how you’ll always start your formula. It tells Google Sheets the function you want to use.
- Text is the cell which will be replaced. The text is also referred to as a string
- Regular expression is the syntax we add to create a REGEX formula
- Replacement is the text we want to replace the original text (or string) with
Don’t worry; this will become clearer as we work our way through the examples.
REGEXREPLACE Google Sheet examples
1. REGEXREPLACE words
We’re going to start nice and easy to get into the swing of things. Have you ever wanted to alter words in a string quickly? REGEXREPLACE makes it a breeze.
Whenever I perform local SEO keyword research, I always check the city location to get more explicit information on search volume. Let’s say I want to find out search volume data for London. I already have a list of core keywords that includes the city “bristol”, so all I need to do is alter “bristol” to “london”.
The formula we need to use:
=REGEXREPLACE(A2,"bristol","london")Okay, let’s breakdown the steps:
Step 1:
To start, we have to target the word we want to alter, which is “bristol”.
Step 2:
I then have to open a new quotation mark, add the word we want to replace “bristol” with, which is “london” and finally add a closing quotation mark.
See. Nice and easy.
2. REGEXREPLACE punctuation
Have you ever had a list that contained punctuation that you wanted to get rid of? If so, REGEXREPLACE will strip it out in no time.
The formula we need to use:
=REGEXREPLACE(A2,"[[:punct:]]","")Step 1:
The key to this formula is using the “[[:punct:]]” syntax, which matches punctuation characters.
Step 2:
We then add empty opening and closing quotation marks to tell our formula we want to replace punctuation with nothing.
3. REGEXREPLACE dates
Something SEOs have to do from time to time is update title tags. And the classic [insert year] format is the type of title tag that gets updated most. For example, travel companies tend to have a load of “best” places to travel [insert year] articles.
I’m going to update all year dates to “2021” in the GIF below.
The formula we need to use:
=REGEXREPLACE(A2,"[0-9]+","2021")Step 1:
To begin, I add “[0-9]” to match any single number between 0 and 9. “[0-9]” matches the year in the title tag.
Step 2:
Secondly, we then need to add the plus “+” symbol, which matches number characters before “+” one or more times. We can now match the full date, instead of matching each digit e.g. “2”, ”0”, ”1”, ”9”.
Step 3:
Finally, we specify the year date that we want to replace the current dates in between quotation marks, which is “2021”.
4. REGEXREPLACE numbers
REGEXREPLACE is useful if you want to extract specific things from strings. For example, let’s say you want to extract numbers from strings that contain alphabetical characters. I’m going to demonstrate this using a few random samples in the GIF below.
The formula we need to use:
=REGEXREPLACE(A2,"[a-zA-Z\s]","")Step 1:
The critical component of this formula is the “[a-zA-Z]” syntax. Crucially, “[a-zA-Z]” matches any lower or upper case alphabetical character. Essentially, we’re matching characters other than digits.
Step 2:
I then added “\s” to the formula, which matches any whitespace character. Basically, “\s” ensures there won’t be any whitespace in our final output when digits aren’t at the beginning of a string. Our formula now looks like “[a-zA-Z\s]”.
Step 3:
Lastly, we add empty opening and closing quotation marks, which instructs our formula to replace alphabetical characters with nothing. Leaving us with just the digits!
5. REGEXREPLACE special characters
REGEXREPLACE can also be used to extract special characters, leaving you with whatever is leftover in a string.
I’m going to demonstrate this by extracting the ”{“, “}”, “@”, “$”, “[“ and “:” characters in the GIF below.
The formula we need to use:
=REGEXREPLACE(A2,"{|}|@|\$|\[|:","")Now, this isn’t as scary as it looks. Let’s walk through the steps together.
Step 1:
So, apart from adding each character we want to extract from a string, you’ll of noticed I added a pipe “|” symbol in between each character—the pipe symbol “|” means “or” in REGEX.
However, you’ll only need to add “|” to your formula if you want to extract more than one symbol. For instance, if you only wanted to remove the “@” character, then your formula would look like this =REGEXREPLACE(A3,"@",""). I just added a couple of characters to better paint the picture.
Step 2:
Additionally, you may have noticed I added a “\” symbol just before “$” and “[“. This is because “$” and “[“ have a special meaning in REGEX, so we need to add a “\”, otherwise known as an escape character, just before these symbols to explicitly match them.
Step 3:
Finally, we add empty opening and closing quotation marks, which instructs our formula to replace these special characters with nothing.
Summary
To sum up, this post walks you through a few REGEXREPLACE Google Sheet examples that should allow you to alter and extract things from strings in no time.
If you want to play around with and debug your REGEX formulas, then I recommend REGEX 101. You’ll need to select the Golang option, as that’s the type of REGEX Google uses in their products like Google Sheets and Google Analytics.
There’s also a ton of REGEX cheat sheets out there. I like to glance at this REGEX cheatsheet.
If you’ve got any cool examples of using REGEXREPLACE in Google Sheets that I haven’t listed, I’d love to see them. Drop a line in the comment section below.
And if you have questions, I’d love to help out. So feel free to ask away.
Happy REGEXing!
