Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSun, 09 Aug 2020 19:54:26 +0200if cell contains this text then [closed]https://ask.libreoffice.org/en/question/259339/if-cell-contains-this-text-then-closed/I saw [this post](https://ask.libreoffice.org/en/question/133479/if-cell-contains-text-then/) but in my case I want to use it with "IF()" (or anything that may give me the result I'm looking for):
Column A has some text | Column B has numbers | for column C, if a cell of column A contains "TJ" within the cell, then write the number this cell has next to it.
Eg:
A | B | C
--|--|--
ABC | 5 |
TJK | 6 | 6
NGB | 0 |
CTJ-2 | 18 | 18
EDIT:
What would be the easier way to include another text condition?
`=IF(OR(ISNUMBER(FIND("TJ";A1));ISNUMBER(FIND("BLA";A1)));B1;"")` maybe?Sun, 09 Aug 2020 18:00:25 +0200https://ask.libreoffice.org/en/question/259339/if-cell-contains-this-text-then-closed/Comment by Moshpirit for <p>I saw <a href="https://ask.libreoffice.org/en/question/133479/if-cell-contains-text-then/">this post</a> but in my case I want to use it with "IF()" (or anything that may give me the result I'm looking for):</p>
<p>Column A has some text | Column B has numbers | for column C, if a cell of column A contains "TJ" within the cell, then write the number this cell has next to it.</p>
<p>Eg: </p>
<pre><code>A | B | C
--|--|--
ABC | 5 |
TJK | 6 | 6
NGB | 0 |
CTJ-2 | 18 | 18
</code></pre>
<p>EDIT:</p>
<p>What would be the easier way to include another text condition?</p>
<p><code>=IF(OR(ISNUMBER(FIND("TJ";A1));ISNUMBER(FIND("BLA";A1)));B1;"")</code> maybe?</p>
https://ask.libreoffice.org/en/question/259339/if-cell-contains-this-text-then-closed/?comment=259363#post-id-259363Great! edit done! :)Sun, 09 Aug 2020 19:24:55 +0200https://ask.libreoffice.org/en/question/259339/if-cell-contains-this-text-then-closed/?comment=259363#post-id-259363Comment by Lupp for <p>I saw <a href="https://ask.libreoffice.org/en/question/133479/if-cell-contains-text-then/">this post</a> but in my case I want to use it with "IF()" (or anything that may give me the result I'm looking for):</p>
<p>Column A has some text | Column B has numbers | for column C, if a cell of column A contains "TJ" within the cell, then write the number this cell has next to it.</p>
<p>Eg: </p>
<pre><code>A | B | C
--|--|--
ABC | 5 |
TJK | 6 | 6
NGB | 0 |
CTJ-2 | 18 | 18
</code></pre>
<p>EDIT:</p>
<p>What would be the easier way to include another text condition?</p>
<p><code>=IF(OR(ISNUMBER(FIND("TJ";A1));ISNUMBER(FIND("BLA";A1)));B1;"")</code> maybe?</p>
https://ask.libreoffice.org/en/question/259339/if-cell-contains-this-text-then-closed/?comment=259359#post-id-259359Since the suggestion from my comment above obviously was the solution you looked for, I will also make an answer of it.
Concerning the new question: You should edit the original question and append the enhanced version there.
My answer will also be enhanced respectively.Sun, 09 Aug 2020 19:13:51 +0200https://ask.libreoffice.org/en/question/259339/if-cell-contains-this-text-then-closed/?comment=259359#post-id-259359Comment by Moshpirit for <p>I saw <a href="https://ask.libreoffice.org/en/question/133479/if-cell-contains-text-then/">this post</a> but in my case I want to use it with "IF()" (or anything that may give me the result I'm looking for):</p>
<p>Column A has some text | Column B has numbers | for column C, if a cell of column A contains "TJ" within the cell, then write the number this cell has next to it.</p>
<p>Eg: </p>
<pre><code>A | B | C
--|--|--
ABC | 5 |
TJK | 6 | 6
NGB | 0 |
CTJ-2 | 18 | 18
</code></pre>
<p>EDIT:</p>
<p>What would be the easier way to include another text condition?</p>
<p><code>=IF(OR(ISNUMBER(FIND("TJ";A1));ISNUMBER(FIND("BLA";A1)));B1;"")</code> maybe?</p>
https://ask.libreoffice.org/en/question/259339/if-cell-contains-this-text-then-closed/?comment=259358#post-id-259358Thanks `=IF(ISNUMBER(FIND("TJ";A1));B1;"")` works perfectly!
What would be the easier way to include another text condition?
`=IF(OR(ISNUMBER(FIND("TJ";A1));ISNUMBER(FIND("BLA";A1)));B1;"")` maybe?Sun, 09 Aug 2020 19:03:38 +0200https://ask.libreoffice.org/en/question/259339/if-cell-contains-this-text-then-closed/?comment=259358#post-id-259358Comment by Lupp for <p>I saw <a href="https://ask.libreoffice.org/en/question/133479/if-cell-contains-text-then/">this post</a> but in my case I want to use it with "IF()" (or anything that may give me the result I'm looking for):</p>
<p>Column A has some text | Column B has numbers | for column C, if a cell of column A contains "TJ" within the cell, then write the number this cell has next to it.</p>
<p>Eg: </p>
<pre><code>A | B | C
--|--|--
ABC | 5 |
TJK | 6 | 6
NGB | 0 |
CTJ-2 | 18 | 18
</code></pre>
<p>EDIT:</p>
<p>What would be the easier way to include another text condition?</p>
<p><code>=IF(OR(ISNUMBER(FIND("TJ";A1));ISNUMBER(FIND("BLA";A1)));B1;"")</code> maybe?</p>
https://ask.libreoffice.org/en/question/259339/if-cell-contains-this-text-then-closed/?comment=259350#post-id-259350If you tell what you want to happen if a condition is met, always **also** tell what should happen otherwise. (And: "next to it" is not precise enough.)
Assume you entered `=IF(ISNUMBER(FIND("TJ";A1));B1;"")` into C1 and filled this formula down to C4 using the little mouse-sensitive handle bottom-right of cell C1: Would the result then be how you want it?Sun, 09 Aug 2020 18:19:39 +0200https://ask.libreoffice.org/en/question/259339/if-cell-contains-this-text-then-closed/?comment=259350#post-id-259350Answer by Lupp for <p>I saw <a href="https://ask.libreoffice.org/en/question/133479/if-cell-contains-text-then/">this post</a> but in my case I want to use it with "IF()" (or anything that may give me the result I'm looking for):</p>
<p>Column A has some text | Column B has numbers | for column C, if a cell of column A contains "TJ" within the cell, then write the number this cell has next to it.</p>
<p>Eg: </p>
<pre><code>A | B | C
--|--|--
ABC | 5 |
TJK | 6 | 6
NGB | 0 |
CTJ-2 | 18 | 18
</code></pre>
<p>EDIT:</p>
<p>What would be the easier way to include another text condition?</p>
<p><code>=IF(OR(ISNUMBER(FIND("TJ";A1));ISNUMBER(FIND("BLA";A1)));B1;"")</code> maybe?</p>
https://ask.libreoffice.org/en/question/259339/if-cell-contains-this-text-then-closed/?answer=259368#post-id-259368(From my comment above: Use `=IF(ISNUMBER(FIND("TJ";A1));B1;"")` in cell C1 and fill the formula down as far as needed (different ways to do so).
Concerning the enhanced question:
There is an infinite multituide of thinkable conditions concerning texts in relation to others. Generally the most powerful tool you can use is "RegularExpression". It is implemented in different ways in Libreoffice, and specifically in Calc.
Restricting my answer to cell formulas searching/comparing texts:
1. If the respective option is enabled under >Tools>Options>LibreOffice Calc>Calculate, some functions will interpret a "search string" as a RegEx. Among these the most relevant ones (imo) are `SEARCH()`, `MATCH()`, `COUNTIF()`. There is no simple way to control this per sheet or per formula. (It's "global"). For the mentioned functions `RegEx` are case-insensitive by default, and you need to use the control sequence (?-i) in the RegEx itself to change that.
2. Starting with version 6.2 there also is the function REGEX() which always works with RegEx (an for wich the default is case-sensitive!).
Concerning `=IF(OR(ISNUMBER(FIND("TJ";A1));ISNUMBER(FIND("BLA";A1)));B1;"")` from the questioners comment above, thinkable solutions are:
`=IF(ISNUMBER(SEARCH("TJ|BLA"; A1));B1; "")` if the RegEx option is ebnabled and insensitive search is intended.
`=IF(ISNUMBER(SEARCH("(?-i)TJ|BLA"; A1));B1; "")` (case sensitive)
<s>`=IF(REGEX(A1;"TJ|BLA";;1)<>"";B1; "")` (case sensitive). The **pair** of semicolons is **not** a typo!
`=IF(REGEX(A1;"(?i)TJ|BLA";;1)<>"";B1; "")` (case insensitive) ....</s>
=IF(IFERROR(REGEX(A1;"TJ|BLA";;1)<>"";0);B1;"") (case sensitive). The **pair** of semicolons is **not** a typo!
=IF(IFERROR(REGEX(A1;"(?i)TJ|BLA";;1)<>"";0);B1;"") (case insensitive)...
<s>Please note that the additional formulas **not** were tested by me, and tell me if they have errors.</s>
Seems I knew there were errors. You are, of course, invited to report additional ones.Sun, 09 Aug 2020 19:38:50 +0200https://ask.libreoffice.org/en/question/259339/if-cell-contains-this-text-then-closed/?answer=259368#post-id-259368Comment by Moshpirit for <p>(From my comment above: Use <code>=IF(ISNUMBER(FIND("TJ";A1));B1;"")</code> in cell C1 and fill the formula down as far as needed (different ways to do so). <br>
Concerning the enhanced question: <br>
There is an infinite multituide of thinkable conditions concerning texts in relation to others. Generally the most powerful tool you can use is "RegularExpression". It is implemented in different ways in Libreoffice, and specifically in Calc. <br>
Restricting my answer to cell formulas searching/comparing texts: <br>
1. If the respective option is enabled under >Tools>Options>LibreOffice Calc>Calculate, some functions will interpret a "search string" as a RegEx. Among these the most relevant ones (imo) are <code>SEARCH()</code>, <code>MATCH()</code>, <code>COUNTIF()</code>. There is no simple way to control this per sheet or per formula. (It's "global"). For the mentioned functions <code>RegEx</code> are case-insensitive by default, and you need to use the control sequence (?-i) in the RegEx itself to change that. <br>
2. Starting with version 6.2 there also is the function REGEX() which always works with RegEx (an for wich the default is case-sensitive!). </p>
<p>Concerning <code>=IF(OR(ISNUMBER(FIND("TJ";A1));ISNUMBER(FIND("BLA";A1)));B1;"")</code> from the questioners comment above, thinkable solutions are:
<code>=IF(ISNUMBER(SEARCH("TJ|BLA"; A1));B1; "")</code> if the RegEx option is ebnabled and insensitive search is intended. <br>
<code>=IF(ISNUMBER(SEARCH("(?-i)TJ|BLA"; A1));B1; "")</code> (case sensitive) <br>
<s><code>=IF(REGEX(A1;"TJ|BLA";;1)<>"";B1; "")</code> (case sensitive). The <strong>pair</strong> of semicolons is <strong>not</strong> a typo! <br>
<code>=IF(REGEX(A1;"(?i)TJ|BLA";;1)<>"";B1; "")</code> (case insensitive) ....</s> <br>
=IF(IFERROR(REGEX(A1;"TJ|BLA";;1)<>"";0);B1;"") (case sensitive). The <strong>pair</strong> of semicolons is <strong>not</strong> a typo! <br>
=IF(IFERROR(REGEX(A1;"(?i)TJ|BLA";;1)<>"";0);B1;"") (case insensitive)... </p>
<p><s>Please note that the additional formulas <strong>not</strong> were tested by me, and tell me if they have errors.</s> <br>
Seems I knew there were errors. You are, of course, invited to report additional ones.</p>
https://ask.libreoffice.org/en/question/259339/if-cell-contains-this-text-then-closed/?comment=259373#post-id-259373Thank you so much! That was a very complete an helpful answer!! :)Sun, 09 Aug 2020 19:54:26 +0200https://ask.libreoffice.org/en/question/259339/if-cell-contains-this-text-then-closed/?comment=259373#post-id-259373