Documentation/Calc Functions/REGEX

Function name:
REGEX

Category:
Text

Summary:
Matches a text string against a regular expression and either extracts or replaces the matched text, depending on argument settings.

Syntax:
REGEX(Text; Expression[; [Replacement][; Flags or Occurrence]])

Returns:
Returns one of the following depending on argument values. For more information about the occurrence of each return type (with examples), see the section below.


 * 1) A copy of a substring extracted from the original text.
 * 2) A copy of the original text, modified by replacing one or multiple matches.
 * 3) A copy of the original text, unmodified.
 * 4) A value not available error (#N/A).

Arguments:
Text is a text string (in quotation marks), or a reference to a cell containing a text string, that is the original text to which Expression is to be applied.

Expression is a text string (in quotation marks), or a reference to a cell containing a text string, that is the regular expression to be applied to Text.

Replacement is a text string (in quotation marks), or a reference to a cell containing a text string, that is the replacement text to be used. Setting Replacement to the empty string can be used as a mechanism to delete matched characters.

Flags or Occurrence is one of the following options:
 * 1) A string comprising a single lowercase "g" character, or a reference to a cell containing such a text string. The "g" character is used to select global replacement, so that any replacement is applied to all matches.
 * 2) A non-negative integer, or a reference to a cell containing such an integer, that indicates which specific match is to be used.


 * If Expression is not a valid regular expression, then REGEX reports an invalid argument error (Err:502).
 * If Flags or Occurrence is a non-numeric text string that contains any character other than a single lowercase "g", then REGEX reports an invalid argument error (Err:502).
 * If Flags or Occurrence is a negative numeric value, then REGEX reports an invalid argument error (Err:502).
 * If Flags or Occurrence is a non-negative, non-integer numeric value, then REGEX uses its floor value.
 * If Replacement is omitted and Flags or Occurrence is set to "g", then REGEX behaves as if both Replacement and Flags or Occurrence were omitted.

Details specific to REGEX function

 * Operation of the REGEX function is not dependent on the option that is currently selected in the Formulas Wildcards area of the (or  on macOS) dialog.


 * The initial default is for REGEX to use case-sensitive matching but this can be changed using flags inside the ICU regular expression. For example, include "(?i)" within the regular expression to change to a case-insensitive match.
 * The original string passed in the Text argument is not affected by any replacements made by REGEX – changes are reflected only in the returned string.


 * It may be helpful to consider REGEX as operating in one of five distinct "modes" depending on the arguments passed to the function. These modes are as follows:


 * 1) REGEX(Text; Expression) extracts the first match of Expression in Text. This simple form can be useful for checking that a string conforms to some predefined pattern (such as, telephone numbers, email addresses, zip codes, etc).
 * 2) * The formula  extracts the first occurrence of a lowercase "c" preceded by any other character and returns the string "ic".
 * 3) * The formula  finds no match and returns #N/A.
 * 4) REGEX(Text; Expression; Replacement) replaces the first match of Expression in Text with Replacement. If there is a match, REGEX returns the modified version of Text; if there is no match, Text is returned unmodified.
 * 5) * The formula  returns "Initialize new variables and optimise your code.".
 * 6) *The formula  finds no match and returns the string "LibreOffice Calc" unmodified.
 * 7) REGEX(Text; Expression; Replacement; "g") replaces all matches of Expression in Text with Replacement. If there is a match, REGEX returns the modified version of Text; if there is no match, Text is returned unmodified.
 * 8) * The formula  returns "Initialize new variables and optimize your code.".
 * 9) * The formula  finds no match and returns the string "LibreOffice Calc" unmodified.
 * 10) REGEX(Text; Expression; ; Occurrence) extracts the nth match of Expression in Text (where n is given by the value of the Occurrence argument).
 * 11) * The formula  extracts the first occurrence of a lowercase "c" preceded by any other character and returns the string "ic".
 * 12) * The formula  extracts the second occurrence of a lowercase "c" preceded by any other character and returns the string "lc".
 * 13) * The formula  finds no match and returns #N/A.
 * 14) * The value of 0 for Occurrence is a special case, for which REGEX returns Text unmodified.
 * 15) REGEX(Text; Expression; Replacement; Occurrence) replaces the nth match of Expression in Text with Replacement (where n is given by the value of the Occurrence argument). If there is a match, REGEX returns the modified version of Text; if there is no match, Text is returned unmodified.
 * 16) * The formula  returns "Initialize new variables and optimise your code.".
 * 17) * The formula  returns "Initialise new variables and optimize your code.".
 * 18) * The formula  returns the string "Initialise new variables and optimise your code." unmodified.
 * 19) * The value of 0 for Occurrence is a special case, for which REGEX returns Text unmodified.

Related LibreOffice functions:
None

ODF standard:
None

Equivalent Excel functions:
None