How do I use a wildcard in an if statement?
 

I want to use a conditional statement to see if a particular cell has a
specific text within a cell. For example:
I want to know if cell A1 contains the text "bag" even though it actually
contains "Large BAG 3".

I want this to work but it does not:
if(A1="*bag*","yes","no")
It should return "yes" since bag is within the cell.
Please help.

You could use:
=IF(ISERROR((FIND("Bag",A1,1))),"no","yes")
But keep in mind that FIND is case sensitive.

You could also use:
=IF(ISERROR((SEARCH("*Bag*",A1,1))),"no","yes")
This would avoid case sensitivity.

tj


"Lenny" wrote:

I want to use a conditional statement to see if a particular cell has a
specific text within a cell. For example:
I want to know if cell A1 contains the text "bag" even though it actually
contains "Large BAG 3".

I want this to work but it does not:
if(A1="*bag*","yes","no")
It should return "yes" since bag is within the cell.
Please help.

I put unnecessary parentheses in those formulas. They work just fine like this:

=IF(ISERROR(FIND("Bag",A1,1)),"no","yes")
=IF(ISERROR(SEARCH("*Bag*",A1,1)),"no","yes")

tj

"tjtjjtjt" wrote:

You could use:
=IF(ISERROR((FIND("Bag",A1,1))),"no","yes")
But keep in mind that FIND is case sensitive.

You could also use:
=IF(ISERROR((SEARCH("*Bag*",A1,1))),"no","yes")
This would avoid case sensitivity.

tj


"Lenny" wrote:

I want to use a conditional statement to see if a particular cell has a
specific text within a cell. For example:
I want to know if cell A1 contains the text "bag" even though it actually
contains "Large BAG 3".

I want this to work but it does not:
if(A1="*bag*","yes","no")
It should return "yes" since bag is within the cell.
Please help.

I don't think tj wanted to add the asterisks to the second example. (It makes
it kind of look like they're required for =Search(), and not required for
=find().)

=IF(ISERROR((SEARCH("Bag",A1,1))),"no","yes")

(but they didn't hurt either.)

I betting that tj was playing with posting this alternative:
=IF(COUNTIF(A1,"*bag*")=0,"no","yes")


tjtjjtjt wrote:

You could use:
=IF(ISERROR((FIND("Bag",A1,1))),"no","yes")
But keep in mind that FIND is case sensitive.

You could also use:
=IF(ISERROR((SEARCH("*Bag*",A1,1))),"no","yes")
This would avoid case sensitivity.

tj

"Lenny" wrote:

I want to use a conditional statement to see if a particular cell has a
specific text within a cell. For example:
I want to know if cell A1 contains the text "bag" even though it actually
contains "Large BAG 3".

I want this to work but it does not:
if(A1="*bag*","yes","no")
It should return "yes" since bag is within the cell.
Please help.

--

Dave Peterson