This is an addition to the similarly named but Closed Question
I managed to get this working with LibreOffice Version: 6.0.7.3 and wanted to share the solution in more detail.
The Original Poster’s Question:
I want to implement classification based on regular expressions.
In sheet1 I haveA B C Item Class Wanted Class apples1 =??? apples apples2 apples apples3 apples Pear A pears Pear B pears Melon other
In sheet2 I have:
A B Regex Class .*apples.* apples .*Pear.* pears .* other
What formula to put in sheet1.B so they have the same values like C?
Does Calc have a function that can be used in formula that makes regular expression replace?
The Solution:
On sheet1 in Cell B2 Put the following:
=INDIRECT("$Sheet2.B"&MATCH(1,COUNTIF(A2,$Sheet2.$A$1:$A$4),0))
*Note: I don’t have the header row “Regex | Class” in my sheet2. I recommend deleting this header row so that the COUNTIF function doesn’t try to evaluate the header row A1
Explanation of what is happening:
COUNTIF() is one of the few functions that can evaluate a regular expression.
Substituting an array of regular expressions doesn’t seem to give COUNTIF() any trouble, by it’s self
=COUNTIF(A2,$Sheet2.$A$1:$A$4)
just evaluates the A2 against Sheet2.A1 but when MATCH() which can take an array as input, is added; COUNTIF() evaluates each expression in turn and MATCH() returns the first that matches. i.e. The index of the first Regular Expression that Matches the string will be returned
If shee1.A3 contained apples and pears
then apples
would be returned because .*apples.*
is the first regular expression to match.
The INDIRECT() function just takes the result of the MATCH() function, which is the index (row) of the regular expression that matched and returns the corresponding value in sheet2 column B.