Excel *Wildcard* Question 2

[Cordury2]

Tried to bump this old thread thread68-679182 but it is now closed...only 5 years old


Is it possible to use the wildcard in an IF formula?

For instance, I have a spreadsheet with text and numbers in the cells, the number of characters varies in the cells so a left, right or mid is out.

I want the formula to look to the cell and if any part of the text contains "ABC" then I want to return 1.

=IF(C18="*ABC*",1,"") Cell C18 containts the letters ABC in order but my formula is still returning blanks?

Any ideas?

 

[Gavona]

thread68-1253790

Gavin

 

[macropod]

Hi Cordury2,

The essential problem you're having with the wildcards is the use of the two asterisks - you can only use one per expression.

You could try:
=NOT(ISERROR(FIND("ABC",C18)))*1
or:
=NOT(ISERROR(SEARCH("ABC",C18)))*1
Either formula will return 1 if 'ABC' exists in the string, and 0 otherwise. The first formula is case-sensitive.

If necessary, you can suppress the 0 via a custom cell format ('0;;') or via an IF test along the lines of:
=IF(ISERROR(FIND("ABC",C18)),"",1)
or:
=IF(ISERROR(SEARCH("ABC",C18)),"",1)
as appropriate.

Cheers

[MS MVP - Word]

 

[Gavona]

Or, from my original link (which contains great wildcard information for a range of functions):
=IF(COUNTIF(A2:A2,"*abc*"),"yes","no")
This only uses 2 functions - IF and CountIf. No idea if that means it would calculate any faster...

Gavin