Google Sheets Regexreplace Function How to and Examples

0
113
Google Sheets Regexreplace Function

Regex Functions give a tough time for Google Sheets users due to the lack of proper guidance. So here I am trying to explain the use of Google Sheets Regexreplace function with the help of several Regexreplace formulas.

There are three Regex Functions using RE2 regular expressions in Google Sheets. They are Regexextract, Regexreplace and Regexmatch. These text functions are very useful for cleaning texts in Google Sheets. But in my experience, Google Sheets users are using other text functions like Find, Substitute, MID etc. to compensate Regex Functions.

In this Google Sheets tutorial or Regexreplace function how to, I am trying to explain some of the common use of Regexreplace with several examples. Needless to say, regular use of Regex is a must to master it. So let’s begin.

The Powerful Google Sheets Regexreplace Function – How to and Examples

Purpose:

The purpose of Regexreplace function is to replace part of a text string with a different text string. For this, Google Sheets Regexreplace function uses RE2 for regular expressions. What is that RE2 regular expressions? Refer the regular expression syntax reference at GitHub. Bookmark that page for your regular reference. When you have a tough time with Regexreplace or other Regex functions in Google Sheets, it would come useful.

How to Use Regexreplace Function in Google Sheets

See the syntax part first.

REGEXREPLACE(text, regular_expression, replacement)

text – The of which will be replaced.

regular_expression – Use to replace matching instances in text.

replacement – The replacement text which will be inserted into the original text.

Formulas to Learn Google Sheets Regexreplace Function

Value in Cell A1: Info Inspired Inspirational 2017 Blog


1. Regexreplace Formula to replace any plain text

Formula: =REGEXREPLACE(A1, “2017”,“2018”)
Result: Info Inspired Inspirational 2018 Blog


2. When you want to replace any number using Regexreplace, use the below formula.

Formula: =REGEXREPLACE(A1, “[0-9]+”,“2018”)
Result: Info Inspired Inspirational 2018 Blog


3. Regexreplace Formula to remove all Spaces

=REGEXREPLACE(A1, “\s”,””)
Result: InfoInspiredInspirational2017Blog

When you want to replace space with any character, follow this formula. Here I’ve used Comma as replacement character.

=REGEXREPLACE(A1, “\s”,”,”)
Result: Info,Inspired,Inspirational,2017,Blog


4. Regexreplace Formula to replace any of the character (this or that)

Formula: =REGEXREPLACE(A1, “o\b”,”ormation”)
Result: Information Inspired Inspirational 2017 Blog


5. Regexreplace Formula to replace Last word of a sentence or certain number of characters from the end. You can either remove it by replacing space or add new text.

Formula: =REGEXREPLACE(A1, “….\z”,””)
Result: Info Inspired Inspirational 2017

Now another formula.

Formula: =REGEXREPLACE(A1, “….\z”,”Professional Blog”)
Result: Info Inspired Inspirational 2017 Professional Blog

OR the below formula is also produce same result

=REGEXREPLACE(A1, “….$”,“Professional Blog”)


6. Regex Replace to replace certain number of characters or word from the beginning.

Formula: =REGEXREPLACE(A1, “^….”,”Newly”)
Result: Newly Inspired Inspirational 2017 Blog


Cell Reference is Cell A2.

I have paid USD 100 for my blog maintenance last month and USD 60 this month

7. Regexreplace formula to replace all the numbers from a text in Google Sheets.

=REGEXREPLACE(A2, “[\d]”,“”)
Result: I have paid USD for my blog maintenance last month and this month it’s USD as forecasted.


Cell Reference is Cell A3.

I have paid USD 100.25 for my blog last month and this month it’s USD 60.50

8. When you want to replace or remove all numbers in a text including decimal places, below is the Regexreplace formula.

=REGEXREPLACE(A3, “[\d\.]+”, “”)
Result: I have paid USD for my blog last month and this month it’s USD

or

=REGEXREPLACE(A6, “[0-9]*\.[0-9]+[0-9]+”,“”)
Result: I have paid USD for my blog last month and this month it’s USD


See the above result. The text USD is still there.

9. You can remove or replace any prefix to a number using Regexreplace as below.

Formula: =REGEXREPLACE(A3, “(\w*)\s[\d\.]+”,””)
Result: I have paid for my blog last month and this month it’s


10. When you want to remove all texts and extract numbers, use the below formula.

=REGEXREPLACE(A3, “[^\d\.]+”, “|”)
Result: |100.25|60.50

When there is no decimal places, the formula would be;

=REGEXREPLACE(A3, “[^\d]+”, “|”)

Please refer our below related tutorial.

How to Extract All Numbers from Text and SUM It in Google Sheets


11. Google Sheets Regex Formula to Replace special characters around a word or text.

Cell A4 Value:

Can you ~replace~ me!

Formula: =REGEXREPLACE(A4, “\~([A-Za-z]+)\~”,”replace”)
Result: Can you replace me!

Cell A5 Value:

Can you <replace> me!

Formula: =REGEXREPLACE(A5, “\<([A-Za-z]+)\>”,”replace”)
Result: Can you replace me!

Or

=REGEXREPLACE(A5, “<(.+)>”,”replace”)


That’s all for now! Open a blank Google sheet, apply our above formulas and go to the RE2 syntax and experiment with different expressions. So that you can be an expert in Google Sheets Regex functions.

LEAVE A REPLY

Please enter your comment!
Please enter your name here