wildcard search within formulae

[Ben Ritchie]

hello,

I would like to use a formula within Excel 2000 to
return "TRUE" if any of the cells in column C have the
word fragment "Refrig" as part of their text eg.

Refrigerate on arrival
Store in a refrigerator

However the following formula,

=IF(C3="*Refrig*",TRUE,FALSE)

returns "FALSE" every time. I do not think that using
asterisks is working correctly as a formula function (I
have only ever got it to work as a search command). I can
only ever get the formula to work if i do any exact match
eg.

=IF(C3="*Refrigerate on arrival*",TRUE,FALSE)

would give "TRUE" for all the "Refrigerate on arrival"s
but not the "Store in the refrigerator"s.

Is there any wildcard function that I could use to do
this job?

Thanks in advance,
Ben

 

[Stephen Dunn]

Hi Ben,

by column (as requested):

=not(isna(match("*Refrig*",C:C,0)))


by cell (as your formula suggests):

=not(iserr(search("*Refrig*",C3)))


HTH
Steve D.

 

[ben ritchie]

its working fine now! many thanks!

-----Original Message-----
Hi Ben,

by column (as requested):

=not(isna(match("*Refrig*",C:C,0)))


by cell (as your formula suggests):

=not(iserr(search("*Refrig*",C3)))


HTH
Steve D.





.

 

[Lance]

countif will use wild cards, the example below will count
the number of words with your phrase.

=COUNTIF(C:C,"*refrig*")

You can use this function in your if statement as

=IF(COUNTIF(C3,"*refrig*")>0,TRUE,FALSE)

Lance