Hi
I’ve enabled RegEx from Options window but I still get #VALUE! for return value.
I’m using following RegEx syntax: ^(face|twitter|google)
And the whole formula has following syntax: =SEARCH("^(face|twitter|google)";E3)
LibreOffice:
Version: 6.0.3.2
Build ID: 1:6.0.3-0ubuntu1
CPU threads: 8; OS: Linux 4.15; UI render: default; VCL: gtk3;
Locale: fi-FI (fi_FI.UTF-8); Calc: group threaded
Hi
#VALUE is the expected value when the searched cell does not contain the desired value.
On my environment (6.1.0.3 (x64) on windows) it’s ok :
HTH - Regards
Doh - thank you 
Is this mentioned somewhere?
Is this mentioned somewhere?
Yes: in ODF OpenFormula specification:
Returns an Error if text not found.
Prepared a patch to add this to help: https://gerrit.libreoffice.org/60891
BTW @mikekaganski: The few programming languages I sufficiently know return 0 (zero) (or -1 if indices zero-based) if an attempt to find “something” “somewhere” fails.
Do you know a sound reason for what it was specified differently for standard functions in Calc?
[Applies to FIND(), SEARCH(), SEARCHB(), MATCH()]
Possibly to help non-programmers to avoid this result being unnoticed (esp. given that too complex formulas with error checking are rare and difficult, and target audience includes largest proportion of non-programmers)?
Using the numeric result (like 0) in further calculations might easily give wrong results if not handled properly.
By the way, IFERROR allows most elegant syntax in this case (compared with any imaginable checking for 0), esp. in single-cell complex formula.
A pseudo-null result would propagate like an error as soon as it is passed in an attempt to access the not found index/position. (Well, not valid if an additional offset is applied.)
However: Usage of IFERROR still blocks interoperability with AOO. And: “it” was done this way long before an IFERROR was implemented in any spreadsheet software.
My assumption: Excel does it this way …
But Excel is evil… (A joke, of course?)
IFERROR is a hint how to check it efficiently, and not meant as a reason for introduction. Looks like you ignored the reason (confirmed by your "Well, not valid …), to emphasise your Excel-related point
If compatibility is the issue, ~no new functions are usable, true. Everyone considers own situation.
Hmmm…
I should melt down my comment above.
Simply read: I’m not convinced there were sound reasons.
I am still convinced: Users should be expected to consider what they do.
I am still convinced …
Well - I don’t see your point here. You seem to declare that how a similar thing is implemented in some programming languages is “sound” enough, and establishes some “standard” deviations from which require big reasons. That isn’t true. Spreadsheet functions aren’t same as programming languages, although one may consider them somewhat similar. For one, the choice of return value here may depend on availability and efficiency of the other option: throwing exceptions.
In spreadsheets, returning an error is just as efficient as assigning 0, while in C++, throwing an exception means large overhead not allowable in such a case. So - given that we can efficiently provide users more convenient and error-proof solution, what are the sound reasons to do otherwise? “That’s how it’s done in language Foo” isn’t sound.
In C++, we introduce range-based loops to avoid errors, although older syntax can do the same. If we can do the same more conveniently, that’s enough reason. And while I do agree that understanding what one does is essential, this shouldn’t force us use brainfuck instead.
Your entrance (“You seem …”) and the second last word aside, I can understand the basic line of your argument. I could even anticipate it to some degree since it is in no way absurd and even common doctrine. And I am well aware of the fact that there are alternatives…
However, I feel sure many decisions concerning the design of Calc/SSSig were bad. Thus I will not simply cease to doubt some as they draw my attention. And I will not start to simply trust in superior wisdom.
Of course - and that is perfectly correct.
last word? I suppose you meant “brainfuck” - if so, then sorry (I only now noticed that this “programming language” specially crafted to be unusable might be unknown and taken as bad word; sorry again)
Now “off view”.
Both of us (@Mike Kaganski and me) don’t know on what rationale the decision factually was based in about 1985.
We should agree, however, that this was long before spreadsheets offered anything similar to a ‘Try - Except’ structure as IFERROR now does.
If somebody actually wants to get explained my preference, please ask jag@psilosoph.de.
My mentioning of some programming languages was (at least) a mistake in “tactics”.