8

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?

0

4 Answers 4

10

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.

4

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)
0
4

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.

2
  • Nice to see an answer to the question rather than debate about the semantic correctness of what's been implemented.
    – gone
    Jul 15, 2022 at 12:02
  • 1
    @gone - Agreed. So many people are bent on debating than answering. Still so much better than forums. I still remember the days before StackOverflow on google groups. Yikes! Jul 15, 2022 at 15:16
1

Whilst the other answers explain why ISNUMBER is behaving this way, no one appers to have given you an actual real world example.

You can use ISNUMBER and VALUE hand in hand to cast your result to a number before you test using ISNUMBER.

For example, let's asume we have a string like MDRLN4885B in A1 and we want to know if the last charater is a non-number, in this case it's a "B".

=ISNUMBER(VALUE(RIGHT(A2, 1)))

This would pretty much give you FALSE (or TRUE if it was a number) which you can then evaluate using IF, for example.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .