# Which functions take regular expressions or patterns?

As I read the help for functions, I'm trying to figure out what type of data a parameter can hold.

Sometimes I see "SearchText", "Text", "FindText" can take the form of a regular expression or pattern, in addition to a literal string.

An example for this question:

SEARCH("FindText"; "Text"; Position)
SUBSTITUTE("Text"; "SearchText"; "NewText"; Occurrence)


I have the option set:

"Enable regular expressions in formulas", but not "Enable wildcards in formulas".

I did see the warnings about not disabling/enabling these options if you want to be compatible with Excel.

Examples:

Don't work:
=SUBSTITUTE("F9","[:digit:]","")
=SUBSTITUTE("F9","[0-9]","")

Work:
=SUBSTITUTE("F9","9","")
=SEARCH("9","F9")
=SEARCH("[:digit:]","F9")
=SEARCH("[0-9]","F9")

edit retag close merge delete

So, in the end, is the real question "give me the exhaustive list of spreadsheet functions that accept regular expressions"? It's any function which is marked to accept regexes in the help. Given the description you gave, it looks like you need the regex function (or else, why would you keep it enabled in options)? If so, you seem to be OK with the incompatibility with Excel.

In version 6.2, there is a new function, REGEX, which is independent of that compatibility setting, so that you can set the setting to Excel-compatible mode (wildcards) and have other formulas return the same values that Excel would, and only use REGEX when you actually need regexes (knowing that using it still is EXCEL-incompatible).

( 2019-02-05 09:46:58 +0100 )edit

Sort by » oldest newest most voted

Functions that support Wildcards or Regular Expressions and depend on the setting under Tools -> Options -> Calc -> Calculate are those that in ODF OpenFormula (ODFF) are defined to depend on the HOST-USE-REGULAR-EXPRESSIONS or HOST-USE-WILDCARDS properties, see https://docs.oasis-open.org/office/v1...

• Database Functions
• DAVERAGE
• DCOUNT
• DCOUNTA
• DGET
• DMAX
• DMIN
• DPRODUCT
• DSTDEV
• DSTDEVP
• DSUM
• DVAR
• DVARP
• Information Functions
• COUNTIF
• COUNTIFS
• Lookup Functions
• HLOOKUP
• LOOKUP
• MATCH
• VLOOKUP
• Mathematical Functions
• SUMIF
• SUMIFS
• Statistical Functions
• AVERAGEIF
• AVERAGEIFS
• Text Functions
• SEARCH
more