https://bugs.documentfoundation.org/show_bug.cgi?id=172694
Bug ID: 172694
Summary: add new optional parameter to REGEX() which alters
nonmatch behaviour in substitutions
Product: LibreOffice
Version: unspecified
Hardware: All
OS: All
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: [email protected]
Reporter: [email protected]
This enhancement permits simple *IN-LINE* alternatives for REGEX substitution
failures.
Currently:
REGEX(Text ; Expression [; [Replacement] [; Flags|Occurrence]] )
Proposed:
REGEX(Text ; Expression [; [Replacement] [;[Flags|Occurrence] [;[0|1]]] )
Presently, REGEX substitution implements the universal regex response to
nonmatching - pass the original input Text unmodified. This design works well
in the imperative program execution paradigm for which it was conceived, but is
less well-fitted for the data-flow and functional programming nature of
spreadsheets.
In other words, a serial program can easily test the result of attempted
substitution and choose alternative execution paths, but a spreadsheet is
constrained to providing alternative values in a data pipeline. Writing such
alternative logic in spreadhsheets is clunky because it requires additional
cells in the data path
A1: =REGEX(T1;E1;R1)
B1: =IF(A1<>T1;A1;"Something different")
or the the grossly inefficient equivalent in a single cell
A1: =IF(REGEX(T1;E1;R1)<>T1,REGEX(T1;E1;R1);"Something different")
The writer could in theory resort to using alternative patterns directly in the
Expression, but the Replacement string becomes instantly ambiguous, and anyone
who has dealt at length with someone else's regex's knows this adds complexity
and an open invitation to the path toward temporary insanity.
What we really want fits the pattern
=IFNA(REGEX(T1;E1;R1);"Something different")
but to maintain compatibility with existing usage we must have
=IFNA(REGEX(T1;E1;R1;;1);"Something different")
Simply stated, we should have a new optional parameter with default value 0
which preserves existing return value for nonmatching result, and instead
returns #NA if the new optional parameter is 1 (or TRUE).
The revised syntax is
REGEX(Text ; Expression [; [Replacement] [;[Flags|Occurrence] [;[0|1]]] )
New argument is a logical value with default (missing) value 0. The new
argument specifies what is returned when Expression does not match and the
Replacement argument exists. Without a Replacement argument, the new argument
is redundant and can be ignored (unless you have better idea).
0: (current behaviour) return Text without modifications.
1: return #NA.
The new behaviour allows the user to return a custom response with little
overhead.
Example: We want to pull out the underlying symbol from options market position
data loaded from a report.
A1: "CALL IBM May 23 2004 83"
A1 contains the option type (CALL or PUT), the underlying stock symbol (IBM),
the option expiry date (May 23 2004) and strike price ($83), a situation which
could occur when a user has insufficient resources to preprocess an input file
into a more spreadsheet-friendly format.
B1: =REGEX(A1;"(CALL|PUT)\s+(\S+)\s+(\S+\s+\d\d\s+\d\d\d\d)\s+(\d+)";"$2")
The Expression causes "CALL" or "PUT" in $1, "IBM" in $2, "May 23 2004" in $3,
and 83 in $4. The result is "IBM".
In the present regime, we do not necessarily have an obvious indication of a
problem with the data if the value of B1 is identical to A1. With the proposed
solution, we could have #NA appear for the result and make it obvious with
conditional formatting for error.
--
You are receiving this mail because:
You are the assignee for the bug.
