EXCEL Search of multiple possible strings of text

Question

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

Try this =IF(OR(ISNUMBER(FIND({"Hello","Hi"},[@Column1]))),"Welcome","") — bosco_yip
– bosco_yip, Commented Feb 5, 2022 at 17:22
@bosco_yip - oops - saw your comment as soon as I answered. Same idea. — mark fitzpatrick
– mark fitzpatrick, Commented Feb 5, 2022 at 17:23
@markfitzpatrick - Nice, basically I prefer using "Search" rather than "Find". — bosco_yip
– bosco_yip, Commented Feb 5, 2022 at 17:31
Note also that FIND() is case sensitive whereas SEARCH() is not. — bugdrown
– bugdrown, Commented Feb 5, 2022 at 17:33
Thank you - and also appreciate the added info that about the difference in FIND and SEARCH — Rob
– Rob, Commented Feb 5, 2022 at 17:55

mark fitzpatrick · Accepted Answer · 2022-02-05 17:23:21Z

1

You can do:

=IF( OR( ISNUMBER( SEARCH( {"Hello","Hi"}, [@Column1]) ) ), "Welcome", "" )

answered Feb 5, 2022 at 17:23

mark fitzpatrick

3,3923 gold badges14 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Givings · Accepted Answer · 2024-09-19 01:11:36Z

0

thanks to mark fitzpatrick on this. I have a set of articles on an ecommerce site that are not in a easily identifiable URL structure, but this worked well: Col: A2 URL: https://www.[domain].com/[PostName] so:=IF(OR(ISNUMBER(SEARCH({"best","bost","how","when","how","tips","cost"},A2))),"Blog",[OtherRulesFormula]) was a simple way to classify URL's as blog posts on a competitor site. Labor analysis intensive, but works.

answered Sep 19, 2024 at 1:11

Givings

292 bronze badges

Collectives™ on Stack Overflow

EXCEL Search of multiple possible strings of text

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related