基本上,我正在尝试编写一个函数来搜索好工作"之类的短语,并以任意顺序返回包含这些单词的单元格-是否他做得好"这工作好吗"或做好上一份工作".
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
更多推荐
有没有一种方法可以在单元格中搜索特定的单词
发布评论