Documentation/Calc Functions/REGEX

From The Document Foundation Wiki
Jump to: navigation, search


Im-jabber.svg

Under construction.
For now, see the system Help files.


Function name:

REGEX

Category:

Text

Summary:

Matches and extracts or optionally replaces text using regular expressions.

Syntax:

REGEX( Text ; Expression [ ; [ Replacement ] [ ; Flags|Occurrence ] ] )

Returns:

Arguments:

Text: A text or reference to a cell where the regular expression is to be applied.

Expression: A text representing the regular expression, using ICU regular expressions. If there is no match and Replacement is not given, #N/A is returned.

Replacement: Optional. The replacement text and references to capture groups. If there is no match, Text is returned unmodified.

Flags: Optional. "g" replaces all matches of Expression in Text, not extracted. If there is no match, Text is returned unmodified.

Occurrence: Optional. Number to indicate which match of Expression in Text is to be extracted or replaced. If there is no match and Replacement is not given, #N/A is returned. If there is no match and Replacement is given, Text is returned unmodified. If Occurrence is 0, Text is returned unmodified.

Additional details:

https://help.libreoffice.org/6.4/en-US/text/shared/01/02100001.html?&DbPAR=CALC&System=WIN

http://userguide.icu-project.org/strings/regexp

Examples:

=REGEX("123456ABCDEF";"[:digit:]";"Z") returns "Z23456ABCDEF", where the first match of a digit is replaced by "Z".

=REGEX("123456ABCDEF";"[:digit:]";"Z";"g") returns "ZZZZZZABCDEF", where all digits were replaced by "Z".

=REGEX("123456ABCDEF";"[126]";"";"g") returns "345ABCDEF", where any occurrence of "1", "2" or "6" is replaced by the empty string, thus deleted.

=REGEX("axbxcxd";".x";;2) returns "bx", the second match of ".x".

=REGEX("axbxcxd";"(.)x";"$1y";2) returns "axbycxd", the second match of "(.)x" (i.e. "bx") replaced with the captured group of one character (i.e. "b") followed by "y".

Related LibreOffice functions:

ODF standard:

Equivalent Excel functions: