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

问题描述

让我们说，在单元格A1中我有拉斯维加斯是伟大的。我想写一个公式，在单元格中查找确切的单词gas。拉斯维加斯≠气体，但我找到的唯一搜索公式：

  = ISNUMBER（SEARCH（gas，lower（A1 ））
  

返回true。有没有做完全匹配的我最好是喜欢不要区分大小写，我相信通过在lower（）中包装A1来满足。

解决方案

我相信正确地覆盖了你的案例必须在天然气和搜索术语之前和之后填充空间，这将确保在单元格的开头或结尾处找到天然气，并防止在任何单词的中间找到气体。不指示文字中是否存在标点符号，但是为了适应搜索周围的标点符号填充空间将无法正常工作，您必须包括气体的情况，气体等可以具体地进行任何标点符号你担心像gas.cost或类似的值可以使用与标点符号相同的填充（ISARCHMBER（gas，&A1）），ISNUMBER（SEARCH（加油站。 ，& A1& ）））

是一个基本搜索，它应该自己返回单词gas或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))

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().

解决方案

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&" ")))

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.

Edit: Dropped a parentheses.

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