REPLACE

This function helps to replace part of a text string with a different text string using regular expressions.

Syntax

SJS.REGEXREPLACE(text, regular_expression, replacement, [modifiers])

This function has the following arguments:

Argument	Description
text	Refers to the text you are trying to replace. The function can input a string, a cell reference, or a cell range reference.
regular_expression	A regular expression that replaces all matching instances of the text.
replacement	Refers to the text that can be inserted in place of original text according to the regular_expression.
modifiers	[Optional] Input to define the pattern. g (global): retains the index of the last match and allows subsequent searches to start from the end of the previous match. i (ignore case): makes the whole expression case insensitive. m (multiline): when enabled, beginning and end anchors are matched with the start and end of a line, instead of the start and end of the whole string. u (unicode): when enabled, you can use extended unicode escapes in the form. s (dotall): dot is matched to any character, including newline.

You can use this function with text (not numbers) as input and return text as output.
If you want numbers as output. Use VALUE function in conjunction with this function.
If you want to use numbers as input, convert them to text using the TEXT function.

SJS.REGEXREPLACE("SpreadJS Worksheet", "s.*t", "book") gives the result SpreadJS Worksheet

SJS.REGEXREPLACE("12/23/2022", "(\d{2})/(\d{2})/(\d{4})", "$3-$1-$2") gives the result 2022-12-23

SJS.REGEXREPLACE("John-Smith", "(\w{4})-(\w{5})", "$2 $1") gives the result Smith John

Errors

returns a #VALUE! error if the value type of text or regular_expression is not text.
returns a #N/A error if the length of the parameters < 3.
returns a #NAME error if any parameter is not valid.