Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
I think I did that. The formula in cells of column C is '=SEARCH("NY",A2)' - without the single quotes. –  TTT Jun 20 '12 at 14:34
    
seems it isn't a formula issue - i've removed my comment. See my answer on the #VALUE! output. –  tohuwawohu Jun 21 '12 at 4:44
add comment

2 Answers

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.

share|improve this answer
add comment

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)
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.