Forum Discussion
IFS with wildcards
I am attempting to extract a word, preferably wildcard style from a cell and then place the desired text in another cell. This is the formula that works for a specific word =IFS(A12="smoke","Smoke D...
Hi itchyfinger
If function in Excel will not work when using wildcards directly after an equals sign in a formula. There is other way to work around with this method.
This formula will solve the issue
=IFS(
ISNUMBER(SEARCH("smoke",A12)),"Smoke Detector",
ISNUMBER(SEARCH("heat",A12)),"Heat Detector",
ISNUMBER(SEARCH("pull",A12)),"Pull Station"
)
Regards, Faraz Shaikh, MCT, MIE, MOS Master, Excel Expert
Amazing, worked well. Thanks so much.
As variant
=IFERROR( CHOOSE(COUNTIFS(A12,"*smoke*")+2*COUNTIFS(A12,"*heat*")+3*COUNTIFS(A12,"*pull*"), "Smoke Detector","Heat Detector","Pull Station"), "no such")
