I am looking to be able to process a large set of data and and find a set of predefined words in a string and return an alternate if found.. I have found i can do this individually with multiple FIND or SEARCH functions inside an IFS statement.. i want to include an OR in there though so i don't have to report as much... EG
Data: Hello World Hi World Goodbye World Bye World
I basically want to just return "Welcome" if either Hello or Hi are found in a cell
I know i can do it with htis: =IFS(ISNUMBER(SEARCH("Hello",[@Column1])),"Welcome",(ISNUMBER(SEARCH("Hi",[@Column1]))),"Welcome",TRUE,"")
But would like a shorter way, since in reality my data set is much larger. I tried this: =IF(ISNUMBER(FIND(OR([@Column1]="Hello",[@Column1]="HI"),"Welcome","")),"") but just get a false response
also, want to keep it out of macros
You can do:
=IF( OR( ISNUMBER( SEARCH( {"Hello","Hi"}, [@Column1]) ) ), "Welcome", "" )