Possible? Desirable? REGEX references

[s.w.s]

A refence to a REGEX expression saved in another cell, as opposed to a text string expression in the REGEX function itself and repeated over many cells in a column

Example

• a named cell HH_MM_SS contains “([01]][:digit:]|2{0-3]:[0-5][:digit:]:[0-5][:digit:]” to validate a time entry

• a named column vector enteredTime contains time texts whose format is to be validated

• a parallel column vector validTime consists of cells with the formula

  =IF (ISERR (REGEX (enteredTime, HH_MM_SS)), "!", "")

When I try this, I get `

502 error

` (running 6.2.8.2 ubuntu 16)

Is it because the expression is ‘compiled’ and then saved in a nontext internal format? If so, adding the capability to reference a REGEX expression would require exposing that compilation process with a function (as is done for dates and times).

[mikekaganski]

Just check your regex. Count opening and closing curly, round and square brackets.

Additional hint: ISNA is better than ISERR when you check return from functions that may return NA; that helps to differentiate from other types of errors that may indicate errors in formulas.

I’d write it as

=IFNA(REGEX(enteredTime;HH_MM_SS);"!")

and the regex would be

(?<=^([01]\d|2[0-3]):[0-5]\d:[0-5]\d)$

And maybe I’d even refrain from creating a separate column here, and just used conditional format for the entry cells, to e.g. color them red when they match the formula (if the entry range starts with A1):

ISNA(REGEX(A1;"^([01]\d|2[0-3]):[0-5]\d:[0-5]\d$"))