LibreOffice Calc SEARCH and FIND functions

I am trying to process some data in Calc. One of the steps involve finding if a certain string is part of one of the column. I tried using FIND and SEARCH functions. Both behave in the same way and I am not getting correct results.

E.g. Say I have following strings in Column A

NY
SF
LON
CAN
US

and am trying to put following formula in column C

=SEARCH("NY",A2)

The result is - cell C2 will have 1 (which is correct) but if the same formula is copied to other cells in column C - it gives me "#VALUE!" error and I am unable to find out why ?

Any one has any ideas?

The result #VALUE! is the default result if the search failes (see the OpenOffice.org Calc documentation on the SEARCH function). Since all other strings don't contain "NY", that's the correct result for those cells.

I assume you mistook the resulting "1" as a "boolean" result, whereas a failed search would result "0". But this isn't the case. The aforementioned documentation has some examples involving ISNUMBER and ISERROR to evaluate the result of the SEARCH function.

Firstly, if you want to search for a text in multiple rows/columns you need to give a range of cells, not just a single cell: A2:A6, or, better $A$2:$A$6.

Secondly, when you copy paste or translate one cell formula to another the $ counts as a constant coordinate. Let's say you put the formula in C2. That copied to C3 will give =SEARCH("NY",A3). If you copied that to D2, the value would be =SEARCH("NY",B2).

From what I read in Libre Office's help, FIND and SEARCH functions search for a text within another text, and I guess it matches the value in the first line of the range you give. So these functions don't do what you expected.

I did some research (trying different things), and it seems your best bet is using MATCH function to check if a value exists in a range of cells and OFFSET to get the value printed and not the index in the range.

=OFFSET($A$2; MATCH("SF";$A$2:$A$6; 0) - 1; 0)

As @tohuwawohu mentioned, use the ISERROR (ISERR) function. When using SEARCH, it returns #VALUE! if the value is not found in the text. To accommodate for this in your logic use the following pattern:

=IF(ISERR(SEARCH(<str_to_find>, <str_to_search_in>)), <NOT_FOUND_do_something>, <IS_FOUND_do_something_else>)

This pattern of using ISERR can be applied to many other cases where a function returns an error. The only exception is #N/A where you must use the ISNA function.