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?
Best Answer
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.