Ask Your Question
0

Which functions take regular expressions or patterns?

asked 2019-02-05 09:19:56 +0100

libreofficeUser30872 gravatar image

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 flag offensive close merge delete

Comments

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

Mike Kaganski gravatar imageMike Kaganski ( 2019-02-05 09:46:58 +0100 )edit

1 Answer

Sort by » oldest newest most voted
2

answered 2019-02-05 13:06:05 +0100

erAck gravatar image

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
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-02-05 09:19:56 +0100

Seen: 29 times

Last updated: Feb 05