Documentation/Calc Functions/REGEX

From The Document Foundation Wiki
Jump to navigation Jump to search


Function name:

REGEX

Category:

Text

Summary:

The function matches and extracts or optionally replaces text using regular expressions.

Syntax:

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

Returns:

Returns a string which is the result of the match, extraction, or replace operation on the Text using some Expression.

Arguments:

Text is a string or reference to a cell containing that string where the regular expression is to be applied.

Expression is a string or reference to a cell containing that string which is the text representing the regular expression, using ICU regular expressions.

Replacement is a string or reference to a cell containing that string which the replacement text to capture groups. If there is no match, Text is returned unmodified.

Flags if equal to "g" then replaces all matches of Expression in Text, not extracted. If there is no match, Text is returned unmodified.

Occurrence is a positive whole number to indicate which match of Expression in Text is to be extracted or replaced. If there is no match and Replacement is given, Text is returned unmodified. If Occurrence is 0, Text is returned unmodified.

  • If there is no match and Replacement is not given, #N/A is returned.

Additional details:

  • To know about the list of regular expressions visit help pages.

Examples:

Formula Description Returns
=REGEX("123456ABCDEF";"[:digit:]";"Z") Here the first match of a digit is replaced by "Z". Z23456ABCDEF
=REGEX("123456ABCDEF";"[:digit:]";"Z";"g") Here all digits are replaced by "Z". ZZZZZZABCDEF
=REGEX("123456ABCDEF";"[126]";"";"g") Here any occurrence of "1", "2" or "6" is replaced by the empty string, thus deleted. 345ABCDEF
=REGEX("axbxcxd";".x";;2) Here the second match of ".x" is returned. bx
=REGEX("axbxcxd","(.)xb",2) If for the same example, the following formula will end up replacing with 2. 2xcxd
=REGEX("axbxcxd";"(.)x";"$1y";2) Here the second match of "(.)x" (i.e. "bx") replaced with the captured group of one character (i.e. "b") followed by "y" is returned. axbycxd
=REGEX("axbxcxd","(.)xbx","$1y",2) For the same example as above but for Expression "(.)xbx" there is no match but we have Replacement hence the same string is returned unmodified. axbxcxd
=REGEX("axbxcxd","(.)xby") For the same example as above but for Expression "(.)xby" there is no match and we also don't have Replacement hence the function returns an error. #N/A

Related LibreOffice functions:

None

ODF standard:

None

Equivalent Excel functions:

None