REGEXREPLACE

Summary

The REGEXREPLACE function replaces text in a string based on a specified regular expression pattern.

Syntax

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

Arguments

Argument	Description
text (required)	The input text or cell reference containing text to replace.
pattern (required)	Regular expression pattern to match.
replacement (required)	Text to substitute for matched patterns
occurrence	Specifies which match to replace: `0`: Replace all (default) Positive N: Replace Nth occurrence Negative N: Replace Nth occurrence from end
case_sensitivity	Case handling: `0`: Case-sensitive (default) `1`: Case-insensitive

Remarks

Returns: The modified string after replacements

Examples

=REGEXREPLACE("0000abcd","0","1", 2) // 0100abcd
=REGEXREPLACE("0000abcd","0","1", 0) // 1111abcd
=REGEXREPLACE("0000abcd","0","1", -1) // 0001abcd
=REGEXREPLACE("aaaAbcd","a","0", ,0)  // 000Abcd
=REGEXREPLACE("aaaAbcd","a","0", ,1)  // 0000bcd
=REGEXREPLACE("Sonia (378) 555-4195  Brown(878) 555-8622", "[0-9]+-", "***-")  // Sonia (378) ***-4195  Brown(878) ***-8622
=REGEXREPLACE("JamesHenry","([A-Z][a-z]+)([A-Z][a-z]+)","$2, $1")  // Henry, James

The REGEXREPLACE function maintains compatibility with Excel's implementation while replacing the legacy SJS.REGEXREPLACE function. Although deprecated, SJS.REGEXREPLACE remains backward compatible. Key differences between the two functions include:

Feature	REGEXREPLACE Function	SJS.REGEXREPLACE Function
Argument Types	Uses numeric values (0/1) for case sensitivity: `=REGEXREPLACE("aaaAbcd","a","0", ,1)`	Uses string modifiers (e.g., "ig"): `=SJS.REGEXREPLACE("aaaAbcd","a","0","ig")`
Instance Replacement	Flexible occurrence control: `0`: Replace all (default) Positive N: Replace Nth occurrence Negative N: Replace Nth occurrence from end `=REGEXREPLACE("aaaAbcd","a","0", 2,1)` `=REGEXREPLACE("aaaAbcd","a","0", -3,1)`	Limited to: First instance (default) All instances (with "g" modifier)
Multiline Handling	Requires standard regex syntax: `=REGEXREPLACE("abc` `ABC","\n[A-Z]+","*")`	Supports "m" modifier for multiline: `=SJS.REGEXREPLACE("abc` `ABC","^[A-Z]+","*","m")`
Non-String Inputs	Accepts all data types: `=REGEXREPLACE(2024,24,25)`	Returns `#VALUE!` for non-string inputs