Identify cell containing given text in LibreOffice Calc

Question

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.

Answer 1

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) is 2, 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:

• the literal a, matching a single a character;
• the dot ., matching any character;
• the asterisk *, 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.

Answer 2

Note to readers: LibreOffice added wildcards and regular expressions, generally compatible with other spreadsheet applications like Excel, around version 5. At the time of this question, I was using the latest version of LO in the Debian repository, which pre-dated that. If you are reading this now, the accepted answer is the best solution. I can think of only one case where you might want to use this answer as an alternative: if you're stuck with an ancient version of LO Calc (which sometimes happens with some featherweight distros that use custom packages). I'll leave this answer in place just in case.

---

Here's a workaround that uses a helper column (it can be used without a helper column if your version of LO Calc supports SUMPRODUCT, which I'll show at the end).

You can hide column B. Cell B1 contains:

=ISNUMBER(SEARCH("a",A1))*ROW()

Copy that down the column as needed. This provides the row number if the cell contains "a", otherwise zero. The cell address that you want is in D1:

=ADDRESS(SUM(B1:B3),1)

The sum of the matching row and a bunch of zeros gives you the row, and the column is known. You could use these values directly inside a formula rather than creating a text representation of the address.

You can eliminate the helper column with SUMPRODUCT, in which case the formula in D1 would be:

=ADDRESS(SUMPRODUCT(ISNUMBER(SEARCH("a",A1:A3))*ROW(A1:A3)),1)

SUMPRODUCT treats the range as an array and does what the helper column does in one step.