Ask Your Question

if cell contains this text then [closed]

asked 2020-08-09 18:00:25 +0200

Moshpirit gravatar image

updated 2020-09-26 11:53:17 +0200

Alex Kemp gravatar image

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.


A | B | C   
ABC | 5 |    
TJK | 6 | 6   
NGB | 0 |    
CTJ-2 | 18 | 18


What would be the easier way to include another text condition?


edit retag flag offensive close merge delete



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?

Lupp gravatar imageLupp ( 2020-08-09 18:19:39 +0200 )edit

Thanks =IF(ISNUMBER(FIND("TJ";A1));B1;"") works perfectly!

What would be the easier way to include another text condition?


Moshpirit gravatar imageMoshpirit ( 2020-08-09 19:03:38 +0200 )edit

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.

Lupp gravatar imageLupp ( 2020-08-09 19:13:51 +0200 )edit

Great! edit done! :)

Moshpirit gravatar imageMoshpirit ( 2020-08-09 19:24:55 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-08-09 19:38:50 +0200

Lupp gravatar image

updated 2020-08-09 19:47:39 +0200

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

edit flag offensive delete link more


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

Moshpirit gravatar imageMoshpirit ( 2020-08-09 19:54:26 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-08-09 18:00:25 +0200

Seen: 154 times

Last updated: Aug 09 '20