The RegExpReplaceAll function

The RegExpReplaceAll function allows you to replace all matches for a regular expression in a string with a different string.

If a string matches a regular expression, you can change all segments of the string that match the regular expression into a common different value. The RegExpReplaceAll function always returns the target string, whether it was modified or not.

Learn more about regular expressions

Regular expressions are well described on the web, but there is not much Excel-specific information since Excel does not support regular expressions. One good starting point is the user guide for JavaScript in the Firefox browser.

There are many web pages that help you test and debug your regular expressions, e.g. regex101.com.

Function reference

Screenshot of the RegExpReplaceAll function

Example

In this example, the regular expression in A2 is matched against the string in A4. There is a match and all segments that are matched are replaced with the new string in A6. The function returns the modified string.

This is what this RegExpReplaceAll function call will look like:

=RegExpReplaceAll(A4,A2,A6,A8)

Format and parameters

=RegExpReplaceAll(string, regex, replacement, flags)

The string is matched against the regular expression, controlled by the optional flags. The function and its results are modeled after the Substitute function in Excel:

  • All segments of the input string that match the regular expression are replaced by the replacement string.
  • If the input string is empty or missing, the function returns an empty string.
  • If there is an input string, but the regex is empty or missing, the function returns the input string.
  • If both a string and a regex are present, but the replacement text is empty or missing, the function replaces all matches with an empty string, i.e. all matches in the string are deleted.

If you need to know if a substitution was made, you must compare the original string with the result of the function, e.g.

=IF(A13=A4, "Unmodified", "Modified")

string

Specify the target string that you want to evaluate against the regular expression.

regex

Specify the regular expression you wish to match. Do not use any special delimiters for the regex, e.g. forward slash.

replacement

A new string that replaces all segments of the target string that match the regular expression.

flags

Specify the flags that control the evaluation of the regular expression (see below). The flags parameter is optional.

Flags with regular expressions

The way the function operates can be controlled by the flags below.

  • If you include a flag in the string, it is turned On.
  • If you omit a flag from the string, it is turned Off.
  • If you provide an empty string, all flags are turned Off.
  • If you don’t provide a flag string, the default flags are imsu

g

Global search, matches the pattern multiple times. Off by default.

i

Case insensitive search, both a and A match a. On by default.

m

Multi-line mode where ^ matches the start of the string and $ matches the end of the entire string. Without this, each line in the string is treated separately. On by default.

s

Short for single line; the . wildcard also matches newline characters. On by default.

u

Treats a pattern as a sequence of Unicode code points. On by default.

y

Perform a sticky search that matches starting at the current position in the target string. Off by default.