excel - Is there a way to search a cell for specific words--regardless of order? -


basically, i'm trying write function search phrase "good job" , return cells including words in order--whether it's "he had job" "is job good" or "good work on last job."

i've tried search , index/match approaches. either return first example, neither returns second two.

is there function this? thanks!

=sumproduct(--isnumber(search({"good","job"},a1)))=2 

will return true if both words present

enter image description here

to use method 2 search words in single cell possible, requires complexity turn 2 words array. if 2 search words in a1, then:

=sumproduct(--isnumber(search(trim(mid(substitute($a$1," ",rept(" ",99)),(row(indirect("1:2"))-1)*98+1,99)),b1)))=2 

enter image description here


Comments

Popular posts from this blog

resizing Telegram inline keyboard -

command line - How can a Python program background itself? -

php - "cURL error 28: Resolving timed out" on Wordpress on Azure App Service on Linux -