Feb 18 2020 11:06 PM
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.
Feb 18 2020 11:47 PM - edited Feb 19 2020 12:57 AM
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
Feb 19 2020 01:10 AM
As variant
=IFERROR(
CHOOSE(COUNTIFS(A12,"*smoke*")+2*COUNTIFS(A12,"*heat*")+3*COUNTIFS(A12,"*pull*"),
"Smoke Detector","Heat Detector","Pull Station"),
"no such")