# 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")

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