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 Detector",A12="heat","Heat Detector",A12="pull","Pull Station") unfortunately if there are any other words in A12 I do not get the desired result. I attempted using the wildcard "*smoke*" and this did not work. The challenge will be if the imported data to the cell looks like SMOKEDETECTOR or HALLSMOKE which are completely likely. I was able to do a (ifsnumber(search( and find it that way, but it did not get the multiplicity of results I was looking for.
3 Replies
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")
