How do i replace regex in google sheets?

The REGEXREPLACE function in Google Sheets is useful if you want to replace a part of a text string with a different text string using regular expressions.

Regular expressions are search patterns or character sequences that allow you to find certain patterns in a given text string. Once you locate a given pattern, they can be replaced as desired with any other text with the REGEXREPLACE function.

The REGEXREPLACE function accepts only text values (and not numbers) as input and outputs text values in return. There are other built-in functions for instances when a number output is desired (read, VALUE function). Else, if the requirement is to replace numbers within a given text string, we need to convert these numbers to text using the TEXT function before passing as inputs to the function.

Let’s take an example.

As part of my market research study, I had floated a consumer survey on “Interior Decor Affinity” to my friends and family. While studying the responses, I noticed that some respondents had given the full state name as to where they resided (within the country), and some others had given just the abbreviation of the state. To summarize such data was a tedious task.

Hence by using the REGEXREPLACE function, I was able to replace all state abbreviations with their respective full names, and the summarization became straightforward.

It’s easy to learn and gets the job done. 

One of the more practical real-world use-cases you will find is the customer registration forms for online websites. Have you ever noticed that when you’re trying to sign up and create an account on your favorite website, irrespective of how you type, all characters end up getting printed in uppercase? The website owners use nothing but regular expression replace function to convert all lower cases to uppercase, for standardization of inputs. 

That’s just one example. There are plenty of other use-cases for the function, such as standardizing phone numbers or correcting cases (upper and/or lower) in a statement. When dealing with text data, the regular expression family of functions is a definite good-to-have since it makes life so much easier.

Great! Let’s dive right into real-business use-cases, where we will deal with actual values and as well as learn how we can write our own REGEXREPLACE function in Google Sheets to efficiently handle our string manipulations.

The Anatomy of the REGEXREPLACE Function

So the syntax (the way we write) of the REGEXREPLACE function is as follows:

=REGEXREPLACE(text, regular_expression, replacement)

Let’s dissect this thing and understand what each of the terms mean:

• = the equal sign is just how we start any function in Google Sheets
• REGEXREPLACE() is our REGEXREPLACE function. REGEXREPLACE will take the original text string (a part of which you want replaced), the regular_expression and the replacement text to give the desired output. All the three inputs are mandatory for the REGEXREPLACE function – there are no stored defaults, which will be used in case you do not provide one of the three inputs required.
• text is the original source string, a part of which you want to replace. This is the field that will be tested for a matching pattern you provide
• regular_expression is the pattern string to search for. This pattern may include literal characters like alphanumeric (letters A through Z, and number 0 through 9), punctuations, blank spaces, etc. The pattern may even include metacharacters, which are certain character symbols with special meanings in finding patterns (for e.g., ‘/’, ‘$’, ‘@’ etc.). An important thing to note is that the search is case-sensitive, which means that uppercase and lowercase alpha characters must be explicitly specified.
• replacement is the string to use to replace all values matching regular_expression

A Real Example of Using REGEXREPLACE Function

Take a look at the example below to see how REGEXREPLACE functions are used in Google Sheets.

As you can see above, the REGEXREPLACE function is handy in formatting text entries and correcting them at places as desired.

One critical thing to take note of is that while specifying the regular_expression in the formula, we could use metacharacters and character classes (as mentioned earlier) to write cleaner and crisper formulae (like how we’ve used in the examples given above).

Google Sheets supports a number of metacharacters that can be used in any of the built-in regular expression functions. I will list down a few of them below:

Metacharacters:

• The character ‘.’ : (dot) Matches any single character, except a new line.
• The character ‘|’: (pipe) used to indicate alteration, that is, an “or”

       For e.g., tea | coffee matches the work tea or coffee

• The character ‘+’: (plus), (used in above example) Matches the preceding character of subexpression one or more times.

      For e.g., \s+ in the above example matches all whitespaces and not just one

• ‘{}’: (curly braces) Matches the specified number of occurrences of the immediately preceding element or metacharacter. You can specify an exact number, a range, or an open-ended range.

      For e.g.: j{3} matches “jjj“, X{0,2}L matches “L“, “XL“, and “XXL“, AB-\d{2,}-YZ matches any alphanumeric identifier with the prefix “AB-“, the suffix “– YZ“, and two or more numbers in the body of the identifier

Character Classes:

• ‘\s’: (used in above example) Matches any whitespace character. For e.g., we can use this character to match a space between two words in a given text string.

      For e.g., tea\scoffee matches the words tea coffee

• ‘\S’: Matches any character that is not a whitespace
• ‘\d’: Matches any digit from 0-9
• ‘\D’: Matches any character that is not a digit from 0-9 
• ‘\w’: Matches any word character – that is, any letter, digit, or underscore. To be specific: a-z, A-Z, 0-9 and _

What will the character class ‘\W’ therefore match, when used as regular_expression? You got it! It matches any non-word character—that is, any character that’s not a letter, digit, or underscore.

You can make a copy of the spreadsheet below and try it for yourself:

The REGEXREPLACE function, when used in combination with other built-in functions, can be an efficient tool to clean, format or restructure text data in a given sheet. For instance, to avoid truncation, we can combine REGEXREPLACE() along with the SUBSTR( ) function to increase field length. 

Let us take a look at how it will work:

Let us say you have a string “x123x”. You want to replace “123” in the string with “ABDCE”. If you use REGEXREPLACE:

REGEXREPLACE(“x123x”, “123”, “ABCDE”) will return “xABCD” as the output.

Now, this is not as desired. You want “xABCDEx” as the output of the formula. Since the length of the original string was five characters, the output of the REGEXREPLACE maintains that length unless explicitly asked not to. And therefore, you combine it with the SUBSTR() function to ensure there is no truncation:

REGEXREPLACE(SUBSTR(“x123x”, 1, 10), “123”, “ABCDE”) will return “xABCDEx”, which is what your desired output is.

Awesome! Let’s begin our REGEXREPLACE function in Google Sheets.

1. Let’s see how to write your own REGEXREPLACE function, step-by-step. Simply click on any cell to make it the active cell. For this guide, I will be selecting B2, where I want to show my result.

1. Next, simply type the equal sign ‘=‘ to begin the function and then followed by the name of the function, which is our ‘regexreplace‘ (or REGEXREPLACE, whichever works).
2. You should find that the auto-suggest box appears with our function of interest. Proceed to enter the first opening bracket ‘(‘. If you get a huge box with text in it, simply hit the arrow on the top-right hand corner of the box to minimize it. You should now see as follows:

       Now, the fun begins! Let’s give the required inputs to the function to remove the unnecessary ‘commas’ from the given text. Our given text is “,,, Cut, Copy,,         Paste” and we want the desired output as “Cut, Copy, Paste”. Let’s give the inputs to the function as we learned from earlier in this document:

1. Notice that we have used a metacharacter “\B” here, to define the regular_expression. The character “\B” indicates “not a word boundary”. Hence, we are asking the function to replace all commas, which do not have any word boundary.
2. Once you have added the required regular_expression and the pattern that you want it to be replaced with, or you have followed what I did, then make sure to close the brackets ‘()’ as shown below.

1. Finally, just hit your Enter key. You’ll find that if you did follow my steps, you should have got Cut, Copy, Paste as the output of the function, which is what is obtained by removing the extra commas.

1. Let’s try another one! This time I’ll be choosing a different text and use a different character class in the regular_expression.

I have used “[0-9]” to match all numbers and then give the replacement as the text “some” to get what I wanted. 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, I have 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.

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).

Let’s try one last example. I want to remove the special character ‘~’ from the given text:

I have selected a regular expression that matches all letters that are wrapped between two ‘~’ to be replaced with the word “replace”. Notice that I’ve given both uppercase and lowercase letters in the regular expression. An alternative to this could be to use the UPPER() function around the A4 cell and use only [A-Z] in the regular expression.

That’s it, well done! You can now use the REGEXREPLACE function together with the other numerous Google Sheets formulas to create even more powerful formulas that can make your life much easier. 🙂

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

How do I search and replace in a regular expression in Google Sheets?

How do you replace in regex?

How do I match a regex in Google Sheets?

How do I replace multiple values in Google Sheets?