# Documentation/Calc Functions/REGEX

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

## 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.

- A copy of a substring extracted from the original text.
- A copy of the original text, modified by replacing one or multiple matches.
- A copy of the original text, unmodified.
- 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:

- 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.
- 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 ▸ ▸ ▸ (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:

- 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.

- The formula
- 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.

- The formula
- 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.

- The formula
- REGEX(
**Text**;**Expression**; ;**Occurrence**) extracts the n^{th}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.

- The formula
- REGEX(
**Text**;**Expression**;**Replacement**;**Occurrence**) replaces the n^{th}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.

- The formula

### General information about Calc's 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