有没有一种方法可以在单元格中搜索特定的单词-无论顺序如何? [英] Is there a way to search a cell for specific words--regardless of order?

问题描述

基本上，我正在尝试编写一个函数来搜索好工作"之类的短语，并以任意顺序返回包含这些单词的单元格-是否他做得好"这工作好吗"或做好上一份工作".

Basically, I'm trying to write a function that will search for a phrase like "good job" and return cells including those words in any order--whether it's "he had a good job" "is this job good" or "good work on your last job."

我尝试了搜索和索引/匹配方法.两者都将返回第一个示例，但都不返回后两个.

I've tried a search and index/match approaches. Either will return the first example, but neither returns the second two.

是否有一个函数可以做到这一点?谢谢！

Is there a function that will do this? Thanks!

推荐答案

=SUMPRODUCT(--ISNUMBER(SEARCH({"good","job"},A1)))=2

如果两个词都出现，

将返回TRUE

will return TRUE if both words are present

可以在单个单元格中对两个搜索词使用此方法，但是将这两个词转换为数组需要一定的复杂性.如果两个搜索词在A1中，则:

To use this method with the two search words in a single cell is possible, but requires some complexity to turn the two words into an array. If the two search words are in A1, then:

=SUMPRODUCT(--ISNUMBER(SEARCH(TRIM(MID(SUBSTITUTE($A$1," ",REPT(" ",99)),(ROW(INDIRECT("1:2"))-1)*98+1,99)),B1)))=2

这篇关于有没有一种方法可以在单元格中搜索特定的单词-无论顺序如何?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！