IF Contains Specific Text

Question

thejayjetson Author

Level 1

11 points

IF Contains Specific Text

Hello, I would to write an IF statement where it looks for specific text.

In essence I am looking for a way to write CONTAINS instead of = when searching a referenced cell.

I do not want a true or false formula, nor do I want to count occurrences of said text.

A1 will contain the word Dog, Frog, or Cat.

The formula cell, B1, should look for the letters "og" together in A1.

If Dog or Frog are in A1, I want B1's cell to answer Yes, but No, for Cat.

Here is what I have found does not work:

IF(A1="og","Yes","No")

IF(A1="*og","Yes","No")

IF(A1=*"og","Yes","No")

This works if the criteria is found, but returns an error if Cat (anything without "og") is in A1:

IF(SEARCH("og",A1),"Yes","No")

Please help me write the correct syntax.

IF this cell contains __, then do this, if not, then do that.

MacBook Air (11-inch Mid 2011), iOS 10.1.1, Numbers

Posted on Nov 24, 2017 8:00 PM

Reply

Answer 1

thejayjetson Author

Level 1

11 points

Nov 25, 2017 10:28 AM in response to HD

First rate HD!

Now to the next thing.

IF(ISERROR(SEARCH("og",A1)),"No","Yes")

Where the heck would I input an OR?

Let's give the scenario to look for "og" OR "on".

If that case the cell would return Yes for Dog, Frog, & Lion but not for Cat, Bat, etc.

Reply

Answer 2

thejayjetson Author

Level 1

11 points

Nov 25, 2017 10:27 AM in response to Barry

Very good Barry! However, HD's formula works better for me so far.

The above is describing what I need in easy terms. Your formula works in the above scenario however it would not work on a cell with a calculation of hours and minutes. HD's did.

Now to the next thing. Using HD's suggested formula

IF(ISERROR(SEARCH("og",A1)),"No","Yes")

where the heck would I input an OR?

Let's give the scenario to look for "og" OR "on".

If that case the cell would return Yes for Dog, Frog, & Lion but not for Cat, Bat, etc.

Reply

Answer 3

thejayjetson Author

Level 1

11 points

Nov 25, 2017 7:13 PM in response to Barry

Well, well, Barry, that works beautifully. I was even able to easily add in another search criteria connected to the AND statement.

However, the end formula for what I am really working on would need to have 10 different criteria which makes for a very long formula.

Could I instead have 10 different cells (in a row I would just hide) typed up and ask the formula to search for a MATCH between C1:C10?

Again, it would not be an exact match; we would merely be looking to see

IF A1 Contains any of the 10 phrases in cell C1:C10 to do this, IF not then do that.

Reply

Answer 4

thejayjetson Author

Level 1

11 points

Nov 25, 2017 7:14 PM in response to HD

Barry's formula works beautifully. I was even able to easily add in another search criteria connected to the AND statement.

However, the end formula for what I am really working on would need to have 10 different criteria which makes for a very long formula.

Could I instead have 10 different cells (in a row I would just hide) typed up and ask the formula to search for a MATCH between let's say C1:C10?

Again, it would not be an exact match; we would merely be looking to see

IF A1 Contains any of the 10 phrases in cell C1:C10 to do this, IF not then do that.

Reply

Answer 5

HD

Level 5

4,945 points

Nov 25, 2017 2:59 PM in response to thejayjetson

Barry's formula does the job.

Reply

Answer 6

thejayjetson Author

Level 1

11 points

Nov 25, 2017 7:29 PM in response to thejayjetson

***I meant column in this case of course.***

Reply

Answer 7

Barry

Level 9

60,023 points

Nov 24, 2017 11:33 PM in response to thejayjetson

Hi Jay,

Here are two formulas. There are probably others:

B2, and filled down column B: IFERROR(IF(FIND("og",A2)>0,"Yes",""),"No")

C2, and filled down column C: IF(LEN(A2)>LEN(SUBSTITUTE(A2,"og","")),"Yes","No")

B2: FIND returns the starting position of the search string ( "og" ) in the string in cell A2.

If the string is found, that position will always be greater than zero, the comparison will return TRUE, and IF will return "Yes". The last argument in IF, "", will never be used, and is included only to keep the syntax correct.

If the search string is not found, FIND will return an error message, IFERROR will catch that error and return "No".

C2: LEN returns the length, measured in characters of what is contained in the parentheses.

LEN(A2) returns the length of the text string in A2.

LEN(SUBSTITUTE(A2,"og","")) returns the length of the same text string after SUBSTITUTE has replaced any occurrences of the string "og" with a null string ( "" ), a length reduced by two characters for each occurrence of that string. If "og" occurs in the text in the cell, the length comparison will return TRUE, and IF will return "Yes". If there are no occurrences of "og" in the cell, SUBSTITUTE will make no changes, the length will remain the same, the comparison will return FLSE, and IF will return "No".

The green fill in the cells containing "Yes" is placed by a conditional highlighting rule. If there's not really a need for the "Yes" or "No" results, that formatting could be applied directly to column A, using a slightly revised rule:

Regards,

Barry

Reply

Answer 8

HD

Level 5

4,945 points

Nov 24, 2017 11:57 PM in response to thejayjetson

Hi thejayjetson,

You have to handle that error. Here's one way:

The formula: IF(ISERROR(SEARCH("og",B2)),"No","Yes")

Which can be filled down for the other cells in column C for the other animals.

Hope it helps,

H

Reply

Answer 9

Barry

Level 9

60,023 points

Nov 25, 2017 11:05 AM in response to thejayjetson

OR is going to throw an error if either "og" or "on" is missing. Since you want the error only if both "og" AND "on" are missing from the cell contents, you'll need to use AND.

IF(AND(ISERROR(SEARCH("og",A2)),ISERROR(SEARCH("on",A2))),"No","Yes")

("London" plays the part of "Lion" in this example.)

I've removed the conditional highlighting rule use in the earlier version of this table.

"The above is describing what I need in easy terms."

As is often the case, the devil's in the details. Better to give an accurate description of the actual issue.

Regards,

Barry

Reply