excel - Wildcard search in array -

i have array containing bunch of common terms used denote field awaiting po. have loop go backwards through large column of data deleting rows value in cell doesn't match of terms in array. use function (which found online) test if value exists in array.

so far good, long value in cell matches value in array exactly. goes wrong if cell contains slight variation on 1 of common terms (ie. "tbc - follow later" or "tbc " instead of "tbc" exactly)

i need way take value in cell , wildcard search against values in array. won't paste code (it's mid-development mess right now), if can below work can apply it.

sub testfilterarray() myarray = array("tbc", "awaiting po", "po follow") if isinarray("tbc xyz", myarray) = false     msgbox "no! item not in array" else     msgbox "yes! item in array" end if end sub  function isinarray(stringtobefound string, arr variant) boolean   isinarray = ubound(filter(arr, stringtobefound)) > -1 end function 

this returns "no!..." "tbc xyz" not being in array i'd return "yes!..." "tbc*" being there if makes sense.

any gratefully appreciated.

function isinarray2(stringtobefound string, myarray variant) boolean isinarray2 = false = lbound(myarray) ubound(myarray)     if "*" & myarray(i) & "*" stringtobefound isinarray2 = true 'will match myarray substring of stringtobefound next end function 

with runtime considerations in mind becomes

function isinarray2(stringtobefound string, myarray variant) boolean isinarray2 = false = lbound(myarray) ubound(myarray)     if "*" & myarray(i) & "*" stringtobefound         isinarray2 = true 'will match myarray substring of stringtobefound         exit function     end if next end function 

thank remark. looking back, yep like statement works other way around, sorry. let me make case , redundancy matcher , test show importance.

function isinarray2(stringtobefound string, myarray variant) boolean isinarray2 = false = lbound(myarray) ubound(myarray)     if lcase(stringtobefound) lcase("*" & replace(myarray(i), " ", "*") & "*")         isinarray2 = true 'will match myarray substring of stringtobefound         exit function     end if next end function  sub testfilterarray() myarray = array("coca cola gmbh", "awaiting po", "po follow") if isinarray2("coca cola deutschland gmbh", myarray) = false     msgbox "no! item not in array" else     msgbox "yes! item in array" end if end sub