Excel 精确词匹配 [英] Excel Exact Word Matching

问题描述

假设我在单元格 A1 中有Vegas is great".我想编写一个公式，在单元格中查找确切的单词gas".Vegas≠gas，但我找到的唯一搜索公式:

Let's say I have "Vegas is great" in cell A1. I want to write a formula that looks for the exact word, "gas" in cells. Vegas ≠ gas, but the only search formula I'm finding:

 =ISNUMBER(SEARCH("gas",lower(A1))

返回真.无论如何要做精确匹配吗?理想情况下，我希望它不区分大小写，我相信通过将 A1 包装在 lower() 中可以满足这一点.

returns true. Is there anyway to do do exact matching? I'd ideally like it to be non-case sensitive which I believe is satisfied by wrapping A1 in lower().

推荐答案

我相信要正确涵盖您必须在术语gas"和搜索术语前后填充空格的情况.这将确保在单元格的开头或结尾找到气体，并防止在任何单词的中间找到它.您的帖子没有指出文件中是否可以存在标点符号，但是为了适应搜索周围的标点符号填充空格将无法正常工作，您必须包含gas"的大小写.gas！"等以允许任何标点符号.如果您担心捕获诸如gas.cost"或类似的值，您可以在标点搜索周围使用相同的填充.

I believe to correctly cover cases you have to pad spaces before and after the term "gas" and the search term. This will ensure that gas will be found at the beginning or end of a cell, and also prevent it from being found in the middle of any words. Your post does not indicate whether punctuation can exist in the file, but to accomodate punctuation padding spaces around the search will not work correctly, you would have to include the case of " gas. " " gas! " etc to allow for any punctuation specifically. If you are worried about catching values like "gas.cost" or similar you can use the same padding around the punctuation search.

=Or(ISNUMBER(SEARCH("gas",""&A1&"")),ISNUMBER(SEARCH("gas.",""&A1&"")))

是一个基本的搜索，它应该单独返回单词 gas 或gas".通过在gas"之后填充一个空格.在搜索中，它会在句子的最后一个单词或单元格的末尾找到它.

Is a basic search that should return the word gas by itself, or "gas." By padding a space after "gas." in the search it will find it as the final word in a sentence, or at the end of a cell.

删除括号.

这篇关于Excel 精确词匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！