Possible? Desirable? REGEX references

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


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

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


and the regex would be


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