How to Use the REGEXREPLACE Google Sheets Function

The REGEXREPLACE Google Sheets function lets you quickly replace parts of strings in your sheets.

It comes as part of a suite of Google Sheet replace string functions along with functions like REGEXEXTRACT and REGEXMATCH.

At first, the inclusion of regular expressions in REGEX functions might seem intimidating, but once you get the hang of it, you will find yourself frequently using them in your spreadsheets.

What Does the REGEXREPLACE Google Sheets Function Do?

The REGEX function simply replaces part of string that matches a particular pattern and replaces it with a different text.

The matching in the REGEXREPLACE function is usually done with regular expressions.

Syntax of the Google Sheets REGEXREPLACE Function

The syntax for the REGEXREPLACE function is as follows:

REGEXMATCH(text, reg_exp, replace_text)

Here,

• text is the text or string, a part of which needs to be replaced.
• reg_exp is a regular expression. The expression matches parts of the text that need to be replaced.
• replace_text is the text that will replace all the parts of text that match the reg_exp.

REGEXREPLACE Examples

The Google Sheets REGEX REPLACE function can be quite helpful when you want to replace a particular search string or pattern in multiple cells.

Here are some useful applications of the REGEXREPLACE function:

• You can use it to replace or remove a particular letter, word or phrase in different cells.
• You can use it to remove or replace all spaces from a text
• You can use it to remove or replace all numerical values in a string
• You can use it to remove or replace all URLS from a string
• You can use it to remove html tags from a string

There are many other ways in which you can apply the REGEXREPLACE function.

Once you start getting comfortable with REGEXREPLACE and regular expressions, you will find that the possibilities are endless.

Let us see how REGEXREPLACE can be used in each of the above applications

Using the Google Sheet Replace Function to Replace or Remove a Letter, Word or Phrase in a String

Let’s first see how you can use REGEXREPLACE to remove a specific letter, word, or phrase from selected cells.

REGEXREPLACE can be quite handy if you want to remove the hash symbol from hashtags in content that had been scraped from social media.

Let us say you have the following text in cell A2 and want to remove all instances of the ‘#’ character from it.

For this, you can use the REGEXREPLACE function as follows:

=REGEXREPLACE(A2,"#", "")

Here’s the result you will get:

Similarly, if you want to replace all instances of a word in a cell with another word, you can use the REGEXREPLACE function.

For example, let us say you want to replace all instances of the word ‘Moscow’ with the word ‘Paris’ in cell A2.

Here’s the Google Sheets replace string formula you can use:

=REGEXREPLACE(A2,"Moscow", "Paris")

And here’s the result you will get:

You can also use REGEXREPLACE to remove or replace any occurrence of more than one word in a cell. For example, say you have the following list of web URLs in cell A2:

Let’s say you want to remove all occurrences of the words ‘.com’, ‘.net’,’.edu’ or ‘.io’ from the contents of cell A2. In that case, you can use the REGEXREPLACE function as follows:

=REGEXREPLACE(A2,".com|.net|.edu|.io","")

Here’s the result you will get:

Using the REGEXREPLACE Function to Remove all Spaces from a String

A common application of the REGEXREPLACE function is to remove spaces from text in a cell.

Let us say you have the following text in cell A2 and you want to remove all the spaces in it to convert it into one big word:

Since space is nothing but a simple character, you can use the REGEXREPLACE function just as we did in the first example:

=REGEXREPLACE(A2," ", "")

Here’s the result you will get:

You could also replace the spaces with another character, for example, a comma:

=REGEXREPLACE(A2," ", ",")

Here’s the result you will get in that case:

Using the REGEXREPLACE Function to Remove or Replace all Numerical values in a String

If you want to replace or remove all numbers from a cell, you need to use the “[0-9]” regex characters.

The square brackets are used to hold a set of characters. Since you want to match any number between 0 and 9, we use the 0-9 regular expression inside the square brackets.

This means ‘match any character that is between 0 and 9’.

Let us say you have the following text in cell A2 and you want to remove all numbers from it:

In that case, your formula should be:

=REGEXREPLACE(A2, "[0-9]","")

Here’s the result you should get:

If you also want to ensure that all numbers, including those with decimal points, get removed, then you can use the following formula:

=REGEXREPLACE(A2, "[0-9]*\.[0-9]|[0-9]","")

Here, we used the ‘.’ character preceded by the escape character ‘\’ so that the ‘.’ character is not mistaken for a regular expression.

The ‘*’ character represents zero or more occurrences of a character or string, while the ‘+’ character represents at least one or more occurrences of a character or string.

This ensures that the regular expression matches even numbers that don’t have any digits before the decimal point.

After the ‘|’ operator we added another [0-9] expression because we also want to consider cases where the number is an integer (with no decimal point at all).

Here’s the result you should get:

Using the REGEXREPLACE Function to Remove or Replace Web URLs from a String

There may be cases where you want to remove all web URLs from a cell. You can do this by using the (.*) expression.

This expression represents any number of characters. When we place this expression between the words ‘www’ and ‘com’, the expression represents any string that starts with www and ends with com.

So to remove all instances of a web URL from cell A2, you can use the formula:

=REGEXREPLACE(A2, "www(.*)com","")

Alternatively, you can also use the following formula:

=REGEXREPLACE(A2, "^www\.[a-zA-Z\.]+com","")

Using the REGEXREPLACE Function to Remove HTML Tags from a String

A common use of the REGEXREPLACE function is to remove special characters around a word.

For example, say you have copied some markup text from a website and need to remove all the HTML tags from it.

REGEXREPLACE makes this really easy. Here’s the formula you can use:

=REGEXREPLACE(A2, "(\<([A-Za-z1-9]+)\>)|(\</([A-Za-z1-9]+)\>)","")

The above formula might look a little complex, but if you break it down, you’ll see it’s actually quite simple. The regular expression in the above formula can mainly be divided into two parts:

• One part deals with instances where you have opening tags, like <p> and <code>
• One part deals with instances where you have closing tags, like </p> and </code>

For the opening tags, we used the regular expression: “(\<([A-Za-z1-9]+)\>)”. This means match all instances that start with the ‘<’ symbol and end with the ‘>’ symbol.

We added an escape character before the two symbols to ensure that they are not mistaken for regular expression symbols.

We also specified that in between these two symbols there could be alphabets in lower or uppercase as well as numbers from 1-9 (if you consider h1, h2,… tags).

For the closing tags, we used the regular expression: “(\</([A-Za-z1-9]+)\>)”. This means match all instances that start with the ‘</’ symbols and end with the ‘>’ symbol.

We also specified that in between these two symbols there could be alphabets in lower or uppercase as well as numbers from 1-9.

The two expressions have been separated with the ‘|’ symbol so that the regular expression matches any of the two sub-expressions.

Here’s the result you should get when you apply the above formula to the text:

Google Sheets REGEXREPLACE Function FAQ

How Do I Use REGEXREPLACE in Google Sheets?

You just have to substitute the replacements you want to make into the syntax. Like so:

REGEXMATCH(text, reg_exp, replace_text)

In this formula:

• text is the location of the string you wish to make replacements in, it’s usually a cell address.
• reg_exp is the text you want to replace. You’d type “M” if you wanted to remove all instances of the letter M
• replace_text is where you put the what you want to replace the text with. For example, ” “ would replace the text with a space.

When to Use REGEXREPLACE?

There are tons of reasons to use the REGEXREPLACE function. To boil it down, it’s when you need to replace parts of text in bulk whether it’s to fix broken calculations or just to correct textual errors.

Google Sheets Replace String Function Points to Remember

Here are a few important points that you need to remember when using the REGEXREPLACE function:

• The REGEXREPLACE function only works with text input. It does not work with numbers. If you want to use numbers as input (for example telephone numbers), you need to first convert it to text, using the TEXT function.
• The function is case-sensitive. Therefore, you will need to specify the correct case inside the regular expression. You may also convert the entire input string to upper or lower case using the UPPER or LOWER functions.
• The REGEXREPLACE function can have numerous applications, once you learn how to use it effectively. We have tried to show you some of these applications in this tutorial.

In order to use this function, good knowledge of regular expressions helps, and the best way to get a good hold over it is to practice.

Play around with different regular expressions and see what results you get.

You will be surprised by how useful the REGEXREPLACE Google Sheets function can be once you start using it in your day-to-day applications.