On Thursday, 2007-06-07 23:40:30 +0300, Leonard Mada wrote:
The current Formula Specification and OOo implementation thereof have a
very weak support for strings. Even fundamental string functions are
lacking This makes the use of ODF a poor choice for many research
fields, where a lot of the data is in text format.
Apart from what Thomas Zander already replied I just want to add that
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)
these (and others) don't need specialized functions that don't return an
error, but instead including an IFERROR(expression;errorcase) function
would be suitable for every type of function/formula returning an error.
In case 'expression' results in an error 'errorcase' is returned,
otherwise the result of 'expression'. We should consider that.
2.) count number of occurrences of a substring within a string, e.g.
COUNTSUBSTR("I11.0; E11.5; I25.5", "I") = 2
3.) SPLIT string into substrings => perform operations on the substrings
SPLIT("I11.0; E11.5; I25.5", ";", IGNORE = " \n\t", SORT = TRUE)
returns an array("E11.5", "I11.0", "I25.5")
SPLIT("I11.0; E11.5; I25.5", ";", IGNORE = " \n\t", SORT = TRUE,
pos = 1)
SPLIT("I11.0; E11.5; I25.5", ";", IGNORE = " \n\t",
RETURN_NUMBER = TRUE)
returns (int) "3"
For many more examples, see any text-oriented programing language.
I'm not sure if we want to standardize on spreadsheet functions that
duplicate a bunch of functionality of text-oriented programming
languages. IMHO such data should be preprocessed using such languages,
or the data be imported through CSV filters of spreadsheet applications
that usually already have exactly this SPLIT operation builtin. Modern
spreadsheet applications also allow users to write their own functions
and/or Add-Ins for specialized needs.