wildcard search within formulae

  • Thread starter Thread starter Ben Ritchie
  • Start date Start date
B

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
 
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.
 
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.





.
Click to expand...
 
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
 
Back
Top