Use REGEXREPLACE to Replace Multiple Values in Google Sheets (An Easy Guide)

While there are a number of ways to do so, the REGEXREPLACE function is a great way to replace multiple values in Google Sheets, especially for string conditions.

The function can take advantage of regular expressions to customize string conditions exactly to the users’ needs.


How to Use REGEXREPLACE to Find and Replace a Value

Note: This section covers the fundamentals of the REGEXREPLACE function. You can skip ahead to the next section for the main methods. However, we recommend you read through this section as well.

Primarily, it is the SUBSTITUTE function that comes to mind when we think about replacing values in Google Sheets. However, the REGEXREPLACE function takes things further by allowing regular expressions to be used to further customize the search results.

The REGEXREPLACE function syntax:

REGEXREPLACE(text, regular_expression, replacement)

regexreplace function syntax for multiple values in google sheets

Let’s use the following dataset to show how we can use the REGEXREPLACE function to replace a value in Google Sheets.

sample worksheet

Here, let’s replace the string “COM” with “XXX”:

Step 1: Open the function and input the cell reference of the source value. In this case, it is cell B2.

opening the regexreplace function and inputting the source cell reference

Step 2: The next field is for the regular expression, but in this case, we will input the text we want to replace, “COM”. The regular expression must be inside quotation marks (“”).

inputting the value that will be replaced

Step 3: Input the replacement string next. For this example, it is “XXX”.

inputting the replacement value in the regexreplace function

You can see a preview of the result of the formula.

Step 4: Close parentheses and press ENTER to apply. Then use the fill handle to apply the formula to the rest of the column.

=REGEXREPLACE(B2,"COM","XXX")

replacing value in google sheets with the regexreplace function

Notice that the formula is case-sensitive. But worry not, there is a way to make it case-insensitive.

Read More: Find and Replace in Google Sheets (3 Ways)


Making REGEXREPLACE Case-Insensitive with Regular Expression

We can easily make the REGEXREPLACE function case-insensitive by using the following regular expression flag:

(?i)

Simply place it in from of the searched string in the regular expression field:

=REGEXREPLACE(B2,"(?i)COM","XXX")

making the regexreplace function case-insensitive in google sheets

With the fundamentals out of the way, let’s now see how we can use the REGEXREPLACE function to replace multiple values in Google Sheets.


Similar Readings


How to Use REGEXREPLACE to Replace Multiple Values in Google Sheets

1. Using OR Condition to Replace Multiple Values with the Same Result in Google Sheets

While the REGEXREPLACE function can be used like the SUBSTITUTE function to replace multiple values, the function has the advantage of being able to use regular expression conditions to operate.

The condition in question is the OR condition, represented by the vertical bar (|).

Here’s a simple example: Replace both “COM” or “ENG” strings with “XXX”, regardless of the case.

With SUBSTITUTE, we’d have to nest the function for each condition:

=SUBSTITUTE(SUBSTITUTE(B2,"COM","XXX"),"ENG","XXX")

And two more nests for the lower-case results.

On the other hand, with REGEXREPLACE the formula is simply:

=REGEXREPLACE(B2,"(?i)COM|ENG","XXX")

using regexreplace function to replace multiple values in google sheets

This is also case-in-sensitive, thanks to the (?i) regular expression flag.

However, it is crucial to understand that using the vertical bar (|) condition is only viable if we are replacing multiple values with the same result, which in this case was “XXX”.


Use the ARRAYFORMULA Function to Instantly Cover a Large Range of Data

Using the ARRAYFORMULA function around our formula gives us three advantages:

  1. We get to input a range of values, making the formula dynamic and accepting new entries.
  2. It allows the user to input the formula only once, saving the need to use the fill handle down the column.
  3. This also reduces the processing time that the application needs.

That said, the first change we need to bring to the formula is to update the data range from B2 to B2:B.

The second is to apply the ARRAYFORMULA function itself around the formula. You can do this easily by putting the text cursor at the end of the formula and pressing CTRL+SHIFT+ENTER on your keyboard. Manual input of the function is also possible.

The formula should look like this:

=ArrayFormula(REGEXREPLACE(B2:B,"(?i)COM|ENG","XXX"))

including the arrayformula function to regexreplace formula to make it more dynamic

Read More: Find Value in a Range in Google Sheets (3 Easy Ways)


2. Using Nested REGEXREPLACE Functions to Replace Multiple Values in Google Sheets

If you have multiple values to replace with other different values, you must resort to the nested function method.

Here, for each different result, we will have one more nested REGEXREPLACE function.

For example, in our existing formula, we want to add another condition. That is to replace the underscores (_) with dashes/hyphens (-).

The formula is:

=ArrayFormula(REGEXREPLACE(REGEXREPLACE(B2:B,"(?i)COM|ENG","XXX"),"_","-"))

using nested regexreplace function to replace multiple values in google sheets

The more conditions you add, the more REGEXREPLACE functions must be included in the nest.


Final Words

The use of the REGEXREPLACE function to replace multiple values in Google Sheets is quite similar to SUBSTITUTE, however, it has some advantages over it mostly in the form of the use of regular expressions.

Regular expressions are a great approach when it comes to customizing searches for strings.

Feel free to leave any queries or advice you might have for us in the comments section below.


Related Articles

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for officewheel.com specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo