libreoffice-calcsearch
Is there a function in LO Calc that will find a cell with the given text?
The search and find functions appear to give a location within a string. That is, if I search for the text "a" within two cells, one of which contains "xxxa" and the other contains "bbb", the answer will be 4.
This is not what I need. I need to find which cell has the text in question.
Replacing GETCELLRANGEFROM(D4) by INDIRECT(D4) should do what you want to achieve (given that D4 contains the string A2:B40). The INDIRECT function "translates" a string into a reference.
Here's a short example how it works:
string value, representating a reference to column A;=SUM(INDIRECT(B1))So, C1 calculates a sum, based on the reference string in B1. Modifying B1 to A1:A7 will change C1, too (resulting in 28). This way, INDIRECT "translates" a string to a reference that can be used in another (surrounding) formula.
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.
Best Answer
Just use the
MATCH()function. Since it supports regular expressions, you can use MATCH() to search for partial strings.Here's an example:
The lookup array is
A1:A4, the search criterion is.*a.*(this is a regular expression, for syntax see link above). The result of the formula=MATCH(".*a.*";A1:A4;0)is2, since the second cell in the lookup array is the first cell that matches the search pattern.EDIT:
Regarding the regular expression: here's a list of regular expression characters. The expression
.*a.*consists of:a, matching a singleacharacter;., matching any character;*, a quantifier, meaning "zero or more", regarding the preceding character.So, the regex pattern matches every cell content containing an
a, preceded and followed by any number of other characters. To get acquainted with regular expressions, check the RegExr or the online regex tester.