Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenFri, 26 Jan 2018 06:07:14 +0100Lookup matching regular expression in Calchttps://ask.libreoffice.org/en/question/14658/lookup-matching-regular-expression-in-calc/I want to implement classification based on regular expressions.
In sheet1 I have
A 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?Fri, 15 Mar 2013 15:22:52 +0100https://ask.libreoffice.org/en/question/14658/lookup-matching-regular-expression-in-calc/Comment by JohnSUN for <p>I want to implement classification based on regular expressions.
In sheet1 I have</p>
<pre><code>A B C
Item Class Wanted Class
apples1 =??? apples
apples2 apples
apples3 apples
Pear A pears
Pear B pears
Melon other
</code></pre>
<p>In sheet2 I have:</p>
<pre><code>A B
Regex Class
.*apples.* apples
.*Pear.* pears
.* other
</code></pre>
<p>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?</p>
https://ask.libreoffice.org/en/question/14658/lookup-matching-regular-expression-in-calc/?comment=14659#post-id-14659Do you mean [this](http://ask.libreoffice.org/en/question/7023/a-method-of-using-regex-to-apply-classifications/#7025)?Fri, 15 Mar 2013 16:11:30 +0100https://ask.libreoffice.org/en/question/14658/lookup-matching-regular-expression-in-calc/?comment=14659#post-id-14659Comment by oweng for <p>I want to implement classification based on regular expressions.
In sheet1 I have</p>
<pre><code>A B C
Item Class Wanted Class
apples1 =??? apples
apples2 apples
apples3 apples
Pear A pears
Pear B pears
Melon other
</code></pre>
<p>In sheet2 I have:</p>
<pre><code>A B
Regex Class
.*apples.* apples
.*Pear.* pears
.* other
</code></pre>
<p>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?</p>
https://ask.libreoffice.org/en/question/14658/lookup-matching-regular-expression-in-calc/?comment=18137#post-id-18137Does the INDEX method proposed in comment #3 above work? I couldn't get it to work here with the named ranges indicated.Mon, 27 May 2013 16:29:09 +0200https://ask.libreoffice.org/en/question/14658/lookup-matching-regular-expression-in-calc/?comment=18137#post-id-18137Comment by nb for <p>I want to implement classification based on regular expressions.
In sheet1 I have</p>
<pre><code>A B C
Item Class Wanted Class
apples1 =??? apples
apples2 apples
apples3 apples
Pear A pears
Pear B pears
Melon other
</code></pre>
<p>In sheet2 I have:</p>
<pre><code>A B
Regex Class
.*apples.* apples
.*Pear.* pears
.* other
</code></pre>
<p>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?</p>
https://ask.libreoffice.org/en/question/14658/lookup-matching-regular-expression-in-calc/?comment=14672#post-id-14672=INDEX(classes.$B$2:$B$10000,MATCH(0,ISERROR(SEARCH(regex,C406)), 0),1). The idea is that we have an array of regular expressions tested against the current row and then the row of the array element that produced a match is returned as a single result. regex is a named range defined as $classes.$A$2:$A$17, the class names are in the B columnFri, 15 Mar 2013 22:32:27 +0100https://ask.libreoffice.org/en/question/14658/lookup-matching-regular-expression-in-calc/?comment=14672#post-id-14672Comment by qubit for <p>I want to implement classification based on regular expressions.
In sheet1 I have</p>
<pre><code>A B C
Item Class Wanted Class
apples1 =??? apples
apples2 apples
apples3 apples
Pear A pears
Pear B pears
Melon other
</code></pre>
<p>In sheet2 I have:</p>
<pre><code>A B
Regex Class
.*apples.* apples
.*Pear.* pears
.* other
</code></pre>
<p>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?</p>
https://ask.libreoffice.org/en/question/14658/lookup-matching-regular-expression-in-calc/?comment=14722#post-id-14722Hi @nb, Could you please post the same content into an Answer? That would help us to resolve this question, and make it easier for any user who reads your question to find your Answer.
Thanks!Sun, 17 Mar 2013 05:00:09 +0100https://ask.libreoffice.org/en/question/14658/lookup-matching-regular-expression-in-calc/?comment=14722#post-id-14722Comment by nb for <p>I want to implement classification based on regular expressions.
In sheet1 I have</p>
<pre><code>A B C
Item Class Wanted Class
apples1 =??? apples
apples2 apples
apples3 apples
Pear A pears
Pear B pears
Melon other
</code></pre>
<p>In sheet2 I have:</p>
<pre><code>A B
Regex Class
.*apples.* apples
.*Pear.* pears
.* other
</code></pre>
<p>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?</p>
https://ask.libreoffice.org/en/question/14658/lookup-matching-regular-expression-in-calc/?comment=14669#post-id-14669Yes, I want to use Calc exactly for the same purpose :)<br>However the solution there is not what is needed as far as I understand. It matches a pattern made of all words of the item against the class. When the item and class contain common words there will be wrong classifications. What is better the class to be a pattern that can be tuned to be unique even containing common words. I managed to get what i want using the array formula:Fri, 15 Mar 2013 22:21:04 +0100https://ask.libreoffice.org/en/question/14658/lookup-matching-regular-expression-in-calc/?comment=14669#post-id-14669Answer by hunteke for <p>I want to implement classification based on regular expressions.
In sheet1 I have</p>
<pre><code>A B C
Item Class Wanted Class
apples1 =??? apples
apples2 apples
apples3 apples
Pear A pears
Pear B pears
Melon other
</code></pre>
<p>In sheet2 I have:</p>
<pre><code>A B
Regex Class
.*apples.* apples
.*Pear.* pears
.* other
</code></pre>
<p>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?</p>
https://ask.libreoffice.org/en/question/14658/lookup-matching-regular-expression-in-calc/?answer=29056#post-id-29056I do not know how @nb solved the problem, but I offer a solution to the problem as described with COUNTIF. There are only a handful of functions that interpret strings as regular expressions, and COUNTIF is [one of them](https://wiki.documentfoundation.org/Documentation/How_Tos/Regular_Expressions_in_Calc). So, treating 1 as a TRUE value, and 0 as a FALSE value:
A B
Item Class
apples1 =IF(COUNTIF(A2, ".*apple.*"), "apples", IF(COUNTIF(A2, ".*pear.*"), "pears", "other")
apples2 =IF(COUNTIF(A3, ".*apple.*"), "apples", IF(COUNTIF(A3, ".*pear.*"), "pears", "other")
apples3 =IF(COUNTIF(A4, ".*apple.*"), "apples", IF(COUNTIF(A4, ".*pear.*"), "pears", "other")
Pear A =IF(COUNTIF(A5, ".*apple.*"), "apples", IF(COUNTIF(A5, ".*pear.*"), "pears", "other")
Pear B =IF(COUNTIF(A6, ".*apple.*"), "apples", IF(COUNTIF(A6, ".*pear.*"), "pears", "other")
Melon =IF(COUNTIF(A7, ".*apple.*"), "apples", IF(COUNTIF(A7, ".*pear.*"), "pears", "other")
The basic IF statement format is `TEST, TRUE_ACTION, FALSE_ACTION`. Since Calc doesn't care if we use multiple lines in the formula, we can break it up into a more manageable version with nested IF functions. Consider:
=IF(TEST1, TRUE_TEST1,
IF(TEST2, TRUE_TEST2,
IF(TEST3, TRUE_TEST3, ... )))
This makes writing the above formula more approachable, in, say, a text editor (Notepad, Gedit, TextWrangler, etc.). For example:
=IF(COUNTIF(A2, ".*apple.*"), "ANSWER IF CONTAINS APPLES",
IF(COUNTIF(A2, ".*pear.*"), "ANSWER IF CONTAINS PEARS",
"UNKNOWN FALLBACK"))
Then, once the formula is written, just fill down.Wed, 29 Jan 2014 22:07:44 +0100https://ask.libreoffice.org/en/question/14658/lookup-matching-regular-expression-in-calc/?answer=29056#post-id-29056Comment by Nicolas Raoul for <p>I do not know how <a href="/en/users/4445/nb/">@nb</a> solved the problem, but I offer a solution to the problem as described with COUNTIF. There are only a handful of functions that interpret strings as regular expressions, and COUNTIF is <a href="https://wiki.documentfoundation.org/Documentation/How_Tos/Regular_Expressions_in_Calc">one of them</a>. So, treating 1 as a TRUE value, and 0 as a FALSE value:</p>
<pre><code> A B
Item Class
apples1 =IF(COUNTIF(A2, ".*apple.*"), "apples", IF(COUNTIF(A2, ".*pear.*"), "pears", "other")
apples2 =IF(COUNTIF(A3, ".*apple.*"), "apples", IF(COUNTIF(A3, ".*pear.*"), "pears", "other")
apples3 =IF(COUNTIF(A4, ".*apple.*"), "apples", IF(COUNTIF(A4, ".*pear.*"), "pears", "other")
Pear A =IF(COUNTIF(A5, ".*apple.*"), "apples", IF(COUNTIF(A5, ".*pear.*"), "pears", "other")
Pear B =IF(COUNTIF(A6, ".*apple.*"), "apples", IF(COUNTIF(A6, ".*pear.*"), "pears", "other")
Melon =IF(COUNTIF(A7, ".*apple.*"), "apples", IF(COUNTIF(A7, ".*pear.*"), "pears", "other")
</code></pre>
<p>The basic IF statement format is <code>TEST, TRUE_ACTION, FALSE_ACTION</code>. Since Calc doesn't care if we use multiple lines in the formula, we can break it up into a more manageable version with nested IF functions. Consider:</p>
<pre><code>=IF(TEST1, TRUE_TEST1,
IF(TEST2, TRUE_TEST2,
IF(TEST3, TRUE_TEST3, ... )))
</code></pre>
<p>This makes writing the above formula more approachable, in, say, a text editor (Notepad, Gedit, TextWrangler, etc.). For example:</p>
<pre><code>=IF(COUNTIF(A2, ".*apple.*"), "ANSWER IF CONTAINS APPLES",
IF(COUNTIF(A2, ".*pear.*"), "ANSWER IF CONTAINS PEARS",
"UNKNOWN FALLBACK"))
</code></pre>
<p>Then, once the formula is written, just fill down.</p>
https://ask.libreoffice.org/en/question/14658/lookup-matching-regular-expression-in-calc/?comment=144129#post-id-144129You are missing parenthesis in the first example.Fri, 26 Jan 2018 06:07:14 +0100https://ask.libreoffice.org/en/question/14658/lookup-matching-regular-expression-in-calc/?comment=144129#post-id-144129