Return value from Search function

[Besto]

Why do you return #VALUE! error value from Search function instead of 0 when the value is not found in the string?

A value not being found in a string is IMO not an error situation - it is a perfectly valid result. You do not see the text search functions in .NET throwing exceptions because the text you search for is not found in the text, you search in.

Returning an error value makes functions depending on the search result more complicated than they need to. For example to use Search in a function that will return the number value from a one column in a specific text if a text value is found in another column, I now have to use this formula:

Code: Select all

=IF(ISERROR(SEARCH("MyText";F3));0;H3)

Returning 0 if value is not found, would make the formula simpler:

Code: Select all

=IF(SEARCH("MyText";F3)>0;H3;0)

I know this change will break formulas in existing worksheets, but it could be made as an option to turn on or off - potentially at worksheet level.

[SuperTech]

Please share the file in which you are getting this error so that we can test it at our end.

If your file is private, please send it to forum[at]softmaker.com with the link of this post.