This post will guide you how to use Google Sheets REGEXREPLACE function with syntax and examples.
Table of Contents
Description
The Google Sheets REGEXREPLACE function is to replace a part of a text string with a different substring using regular expressions.
The REGEXREPLACE function can be used to replace a substring from a text string according to a given regular expression in google sheets.
The REGEXREPLACE function is a build-in function in Google Sheets and it is categorized as a Text Function. And The REGEXREPLACE function is one of the three REGEX functions (REGEXEXTRACT, REGEXMATCH and REGEXREPLACE )in google sheets.
Syntax
The syntax of the REGEXREPLACE function is as below:
=REGEXREPLACE (text, regular_expression, replacement)
Where the REGEXREPLACE function arguments are:
- text -This is a required argument. The text string that you want to extract a substring based on regular expression.
- regular_expression – This is a required argument. The
Regular_expressionis a regular expression. It can be used to match the part of the text that you want to extract. The regular expression argument should be entered in double-quote characters. AndRegular_expressionis a search patterns that allow you to find a certain patterns in original text string. - Replacement – The text which will be inserted into the original text.
Note:
- The REGEXREPLACE function takes two arguments. It checks whether a piece of text matches a given regular expression.
- The REGEXREPLACE function only works with text value as its first argument and returns a text result. If you wish to return a numeric value, and you need to use VALUE function in combination with the REGEXREPLACE function. If the input value is a number, and you can use the TEXT function to convert them.
- Google Sheets supports RE2 for regular expressions.
Google Sheets REGEXREPLACE Function Examples
The below examples will show you how to use google sheets REGEXREPLACE function to replace a substring from a text according to a regular expression.
#1 Replace all “google” keywords with “mygoogle” text string, and you can use the REGEXREPLACE function with a regular expression “google”, type:
=REGEXREPLACE(B1,"google","mygoogle") //it returns “mygoogle 2021 sheets 2020”
#2 Replace all word with “mygoogle” substring from a string in cell B1, and you can use the REGEXREPLACE function with a regular expression “\w+”, type:
=REGEXREPLACE(B1,"\w+","mygoogle") //it returns “mygoogle mygoogle mygoogle mygoogle”
#3 Replace the last whole word from a string using REGEXREPLACE function with a regular expression “\w+$”, type:
=REGEXREPLACE(B1,"\w+$","mygoogle") // it returns “google 2021 sheets mygoogle”
#4 Replace all numbers with “2025” from a string in cell B1 and you can use the REGEXREPLACE function with a regular expression “\d+”, type:
=REGEXREPLACE(B1,"\d+","2025") // it returns “google 2025 sheets 2025”
#5 Replate the last number with “2025” number from a string in cell B1, and you can use the REGEXREPLACE function with a regular expression “\d+$”, type:
=REGEXREPLACE(B1,"\d+$","2025") // it returns “google 2021 sheets 2025”
