Documentation/Calc Functions/REGEX

    From The Document Foundation Wiki
    This page contains changes which are not marked for translation.

    TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

    Other languages:


    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 #Additional details: 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.

    Additional details:

    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 Tools ▸ Options ▸ LibreOffice Calc ▸ Calculate (or LibreOffice ▸ Preferences ▸ LibreOffice Calc ▸ Calculate 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, like in =REGEX("test","(?i)"& "TEST").
    • 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).
      • The formula =REGEX("LibreOffice Calc"; ".c") extracts the first occurrence of a lowercase "c" preceded by any other character and returns the string "ic".
      • The formula =REGEX("LibreOffice Calc"; "Z") finds no match and returns #N/A.
    2. 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.
      • The formula =REGEX("Initialise new variables and optimise your code."; "ise"; "ize") returns "Initialize new variables and optimise your code.".
      • The formula =REGEX("LibreOffice Calc"; "Z"; "?") finds no match and returns the string "LibreOffice Calc" unmodified.
    3. 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.
      • The formula =REGEX("Initialise new variables and optimise your code."; "ise"; "ize"; "g") returns "Initialize new variables and optimize your code.".
      • The formula =REGEX("LibreOffice Calc"; "Z"; "?"; "g") finds no match and returns the string "LibreOffice Calc" unmodified.
    4. REGEX(Text; Expression; ; Occurrence) extracts the nth match of Expression in Text (where n is given by the value of the Occurrence argument).
      • The formula =REGEX("LibreOffice Calc"; ".c"; ; 1) extracts the first occurrence of a lowercase "c" preceded by any other character and returns the string "ic".
      • The formula =REGEX("LibreOffice Calc"; ".c"; ; 2) extracts the second occurrence of a lowercase "c" preceded by any other character and returns the string "lc".
      • The formula =REGEX("LibreOffice Calc"; ".c"; ; 3) finds no match and returns #N/A.
      • The value of 0 for Occurrence is a special case, for which REGEX returns Text unmodified.
    5. 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.
      • The formula =REGEX("Initialise new variables and optimise your code."; "ise"; "ize"; 1) returns "Initialize new variables and optimise your code.".
      • The formula =REGEX("Initialise new variables and optimise your code."; "ise"; "ize"; 2) returns "Initialise new variables and optimize your code.".
      • The formula =REGEX("Initialise new variables and optimise your code."; "ise"; "ize"; 3) returns the string "Initialise new variables and optimise your code." unmodified.
      • The value of 0 for Occurrence is a special case, for which REGEX returns Text unmodified.


    General information about Calc's regular expressions

    Note pin.svg

    Note:
    For convenience, the information in this subsection is repeated on all pages describing functions that manipulate regular expressions.
    • A regular expression is a string of characters defining a pattern of text that is to be matched. More detailed, general background information can be found on Wikipedia’s Regular expression page.
    • Regular expressions are widely used in many domains and there are multiple regular expression processors available. Calc utilises the open source Regular Expressions package from the International Components for Unicode (ICU), see their Regular Expressions documentation for further details, including a full definition of the syntax for ICU Regular Expressions.
    • In addition, the LibreOffice Help system provides a high-level list of regular expressions.
    • Calc’s regular expression engine supports numbered capture groups, which allow sub-ranges within a match to be identified and used within replacement text. Parentheses are used to group components of a regular expression together and create a numbered capture group. To insert a capture group into a replacement text, use the "$n" form, where n is the number of the capture group.

    Examples:

    Formula Description Returns
    =REGEX("123456ABCDEF"; "[:digit:]"; "Z") Here the function returns "Z23456ABCDEF", where the first match of a digit is replaced by "Z". Z23456ABCDEF
    =REGEX(D1; D2; D3; D4) where cells D1 to D4 contain the strings "123456ABCDEF", "[:digit:]", "Z", and "g" respectively. Here the function returns "ZZZZZZABCDEF", where all digits are replaced by "Z". ZZZZZZABCDEF
    =REGEX("123456ABCDEF"; "[126]"; ""; "g") Here the function returns "345ABCDEF", where any occurrence of "1", "2", or "6" is replaced by the empty string, and thus deleted. 345ABCDEF
    =REGEX("axbxcxd"; ".x"; ; 2) Here the function returns "bx", the second match of ".x". bx
    =REGEX("axbxcxd"; "(.)x"; "$1y"; 2) Here the function returns "axbycxd", the second match of "(.)x" (i.e. "bx") is replaced with the captured group of one character (i.e. "b") followed by "y". axbycxd
    =REGEX("LibreOffice Calc"; "libreoffice") Here the function returns #N/A because by default matching is case-sensitive. #N/A
    =REGEX("LibreOffice Calc"; "(?i)libreoffice") Here the function finds a match and returns "LibreOffice", because the (?i) search flag switches to a case-insensitive match. LibreOffice

    Related LibreOffice functions:

    None.

    ODF standard:

    None

    Related (or similar) Excel functions:

    None


    Retrieved from "https://wiki.documentfoundation.org/index.php?title=Documentation/Calc_Functions/REGEX&oldid=772543"