If cell contains this text then [closed]

Moshpirit · August 9, 2020, 4:00pm

I saw this post 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?

Lupp · August 9, 2020, 4:19pm

If 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?

Moshpirit · August 9, 2020, 5:03pm

Thanks =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?

Lupp · August 9, 2020, 5:13pm

Since 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.

Moshpirit · August 9, 2020, 5:24pm

Great! edit done!

Lupp · August 9, 2020, 5:38pm

(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:

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.
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)
=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) …
=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)…

~~Please note that the additional formulas not were tested by me, and tell me if they have errors.~~
Seems I knew there were errors. You are, of course, invited to report additional ones.

Moshpirit · August 9, 2020, 5:54pm

Thank you so much! That was a very complete an helpful answer!!