REGEXREPLACE function

REGEXREPLACE returns a new text string where parts matching a regular expression are replaced with a specified replacement.

=REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity])

Generate a REGEXREPLACE formula

Describe what you need. The generator will reach for REGEXREPLACE where REGEXREPLACE is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How REGEXREPLACE reads its arguments
textrequiredpatternrequiredreplacementrequiredoccurrenceoptionalcase_sensitivityoptionalREGEXREPLACE →
ArgumentRequiredDescription
textRequiredString or cell reference containing the original text; if omitted or not text, #VALUE! is returned.
patternRequiredA valid regular-expression pattern; must be a string, otherwise #VALUE! occurs.
replacementRequiredThe string to insert for each match; can be empty to delete matched text.
occurrenceOptionalOptional positive integer indicating which match to replace; defaults to all matches, zero or negative triggers #NUM!.
case_sensitivityOptionalOptional 0 (default, case-insensitive) or 1 (case-sensitive); any other value returns #VALUE!.

Returns

Returns a single-cell text string (or an array of strings when used with spill).

Availability

Excel: 365 (2024+) · Google Sheets: Supported

Worked examples

1. Strip ticket ID prefix

Ticket IDPriorityOpenedClosedAgentCSAT
TCK-001High2023-07-01 09:152023-07-01 10:02Alice5
=REGEXREPLACE(A2, "^TCK-", "")

Result: 001

The pattern ^TCK- matches the literal characters "TCK-" at the start of the string in A2. REGEXREPLACE replaces that match with an empty string, leaving only the numeric part of the ticket identifier.

2. Convert spaces in priority to hyphens

Ticket IDPriorityOpenedClosedAgentCSAT
TCK-004Very High2023-07-04 08:202023-07-04 12:45Dana4
=REGEXREPLACE(B2, "\\s+", "-")

Result: Very-High

The pattern \s+ finds one or more whitespace characters inside the priority text. Each group of spaces is replaced by a hyphen, turning "Very High" into "Very-High" while leaving other characters untouched.

3. Mask all but the first digit of a CSAT score

Ticket IDPriorityOpenedClosedAgentCSAT
TCK-005Medium2023-07-05 14:002023-07-06 09:30Eli10
=REGEXREPLACE(F2, "(?<=\\d)\\d+", "*")

Result: 1*

The look-behind (?<=\d) ensures the match starts after the first digit, and \d+ captures the remaining digits. Those captured digits are replaced by a single asterisk, so "10" becomes "1*" while a single-digit score such as "5" would remain unchanged.

Common errors

Which REGEXREPLACE error are you seeing?
REGEXREPLACE returned an error#VALUE!
Correct the regex syntax, for example change "(abc" to "(abc)".
#NUM!
Provide a positive integer (e.g., 1) or omit the argument to replace all matches.
#REF!
Adjust the reference so it points to an existing cell containing the source text.
ErrorWhy it happensHow to fix it
#VALUE!The pattern argument contains an invalid regular expression, such as an unmatched parenthesis.Correct the regex syntax, for example change "(abc" to "(abc)".
#NUM!The optional occurrence argument is zero or a negative number, which is not allowed.Provide a positive integer (e.g., 1) or omit the argument to replace all matches.
#REF!The text argument refers to a cell that has been deleted or points to an invalid range.Adjust the reference so it points to an existing cell containing the source text.

Tips and when to use something else

  • Use SUBSTITUTE when you only need a literal string replacement; it is faster and does not require regex syntax.
  • Combine REGEXREPLACE with TEXTJOIN to clean up multi-line notes in a single cell.
  • Set case_sensitivity to 1 when you must distinguish between upper- and lower-case characters in the pattern.
  • When you need to replace only the nth occurrence, supply the occurrence argument; otherwise leave it blank to replace every match.

Related functions

SUBSTITUTESUBSTITUTE returns a new text string where specified characters or substrings are replaced, optionally limiting the change to a particular occurrence.TEXTJOINTEXTJOIN merges multiple text strings or ranges into one string, using a chosen delimiter and optional ignoring of empty cells.LEFTLEFT returns the leftmost characters from a text string, optionally limited by a specified number of characters.FINDFIND returns the numeric position of a substring within a text string, counting from a specified start point and respecting case sensitivity.XLOOKUPXLOOKUP returns the value from a specified return array that corresponds to a given lookup value, with optional handling for missing matches and search direction.

Frequently asked questions

How do I replace only the first occurrence of a word in a cell?
Supply the occurrence argument as 1. For example, =REGEXREPLACE(A2, "word", "new", 1) replaces only the first "word" found in A2.
Can REGEXREPLACE be used to remove line breaks?
Yes. Use the pattern "\r?\n" to match Windows or Unix line breaks and replace them with an empty string: =REGEXREPLACE(A2, "\r?\n", "").
Why does REGEXREPLACE return the original text when there is no match?
If the pattern finds no matches, REGEXREPLACE simply returns the unaltered text argument; this behavior lets you chain the function without extra error handling.
What is the difference between REGEXREPLACE and SUBSTITUTE?
REGEXREPLACE works with regular-expression patterns, allowing complex matching like character classes and look-arounds, while SUBSTITUTE only replaces exact literal strings and cannot use regex features.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator