Ask Your Question

SEARCH function with RegEx parameter returns #VALUE!

asked 2018-09-22 09:53:42 +0100

JAKE gravatar image

updated 2018-09-22 09:55:01 +0100


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

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-09-22 10:19:13 +0100

pierre-yves samyn gravatar image


#VALUE is the expected value when the searched cell does not contain the desired value.

On my environment ( (x64) on windows) it's ok :

image description

HTH - Regards

edit flag offensive delete link more


Doh - thank you :) Is this mentioned somewhere?

JAKE gravatar imageJAKE ( 2018-09-22 10:33:10 +0100 )edit

Is this mentioned somewhere?

Yes: in ODF OpenFormula specification:

Returns an Error if text not found.

Prepared a patch to add this to help:

Mike Kaganski gravatar imageMike Kaganski ( 2018-09-22 10:51:09 +0100 )edit

BTW @Mike Kaganski: 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()]

Lupp gravatar imageLupp ( 2018-09-22 11:15:43 +0100 )edit

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.

Mike Kaganski gravatar imageMike Kaganski ( 2018-09-22 11:19:10 +0100 )edit

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

Lupp gravatar imageLupp ( 2018-09-22 11:55:32 +0100 )edit

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.

Mike Kaganski gravatar imageMike Kaganski ( 2018-09-22 12:24:33 +0100 )edit

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.

Lupp gravatar imageLupp ( 2018-09-22 12:27:05 +0100 )edit

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.

Mike Kaganski gravatar imageMike Kaganski ( 2018-09-22 13:02:58 +0100 )edit

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.

Mike Kaganski gravatar imageMike Kaganski ( 2018-09-22 13:08:40 +0100 )edit

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.

Mike Kaganski gravatar imageMike Kaganski ( 2018-09-22 13:14:08 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-09-22 09:53:42 +0100

Seen: 34 times

Last updated: Sep 22 '18