OASIS Mailing List ArchivesView the OASIS mailing list archive below
or browse/search using MarkMail.


Help: OASIS Mailing Lists Help | MarkMail Help

office-formula message

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]

Subject: Re: [office-formula] Re: Very Weak String Support in ODF

Leonard Mada posted the following in the public comments section:
> >1.) an extension to FIND() and SEARCH() that returns '0' IF string is  NOT found, instead of the '#NA!" (greatly eases work with complex searches)

I think we need to be very cautious about adding new functions at this stage, but we also need to consider comments seriously. And this comment in particular points out something more fundamental that I hadn't considered before, and suggests something REALLY useful.  Please hear me out...

Basically, this request points out a much more general problem in spreadsheet formula languages like OpenFormula. If you want to express "If complicated_expression X has value Y, return Z, else return complicated_expression X", there really isn't a good way to do it.  The IF() command doesn't do the job, because you have to repeat complicated_expression twice (essentially).  The only REAL way to do this is to use multiple cells: one to compute X, and another to use X twice.  But that's nasty if you need to do it often (say, to replace NA() with 0 many times).

Most functional languages have a let x= ...., enabling reuse of x, which solves this and many other problems.  But I think trying to add a "let" expression at this stage is rediculously dangerous, because there's NO experience in doing that in spreadsheet formula languages.

I think we can create a single function or two that is MUCH more general than the poster's request, and does what he needs.  How about a function like:
 Any IFEQ(Any x [; Any y=NA() [; Any z=0]])

Semantics: Compute x.  Then, if x=y, return z; otherwise return x.  As a special case, if y is an Error value, the equality test is only considered True if x is the same Error value, otherwise it is considered False.  Thus, IFEQ(NA()) and IFEQ(1/0;1/0) return 0.

So IFEQ(FIND(...)) would be the same as the new FIND function the poster wanted, and this function could be used in lots of other situations too.  A better name would be nice. I would add this to the "Large" set, though "Medium" is plausible.

> >2.) count number of occurrences of a substring  within a string, e.g.
> >       COUNTSUBSTR("I11.0; E11.5;  I25.5", "I") = 2

This one is pretty straightforward, so I have no basic objections to adding this function.  Given that implementations already need to implement FIND, this should be really easy to implement.  Large or Medium, I think.  I'd be happy with this one.

> >3.) SPLIT string into substrings => perform operations on the substrings

This one involves variable-length arrays, and makes me very nervous about adding it at this late stage.  The _functionality_ is clearly nice, but I'd want to hear from multiple implementors before doing anything like this.

--- David A. Wheeler

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]