Excel: search multiple strings then return value

Question

I am using the following formula to search for a string and return a value

IF(ISNUMBER(SEARCH("CHA*",B:B),"CINEMA","")

If I want to search for multiple strings whats the formula?, I've tried the following but it isn't returning anything

IF(ISNUMBER(SEARCH({"odeon*","vue*"},B:B),"CINEMA","")

Taosique · Accepted Answer · 2016-04-20 11:43:38Z

1

Try this:

=IF(COUNT(SEARCH({"odeon*","vue*"},B:B))>0,"CINEMA","")

Another way:

=IF(OR(ISNUMBER(SEARCH({"odeon*","vue*"},B:B))),"CINEMA","")

Use AND() if you require ALL substrings to match:

=IF(AND(ISNUMBER(SEARCH({"odeon*","vue*"},B:B))),"CINEMA","")

answered Apr 20, 2016 at 11:43

Taosique

7,9143 gold badges33 silver badges60 bronze badges

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

Comments

watakushi · Accepted Answer · 2016-04-20 13:05:12Z

0

For starters, you're missing a ) on either formula. And for them to work anyway, you should, as Taosique pointed out, use =IF(OR(ISNUMBER(SEARCH( if you need just one substring, or =IF(AND(ISNUMBER(SEARCH( if you require both.

Good luck!

answered Apr 20, 2016 at 13:05

watakushi

519 bronze badges

Collectives™ on Stack Overflow

Excel: search multiple strings then return value

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