☰

如何在 Excel 中进行部分字符串比较？

tag-icon • tag-icon microsoft-excel
如何在 Excel 中进行部分字符串比较？

我在 Excel 中有两列。两列都有一个以随机字符为后缀的字符串列表。前缀的格式是两个字符串后加一个下划线，后缀也加一个下划线。

IE

ABC_DEF_a => (prefix = "ABC_DEF", suffix = "a") HJDSGDJ_KJ1_a10 => (prefix = "HJDSGDJ_KJ1", suffix = "a10"

如果 B 列中的前缀与 A 列中的前缀相同，该如何比较？

编辑：我知道我可以通过将字符串拆分为三个部分，然后将前两个部分合并为一列，然后使用 VLOOKUP 检查该列是否在另一列中来实现此目的。但我正在寻找一个单行函数。

答案1

我假设在单元格 A1 和 B1 中您有：

在此处输入图片描述

在 C 列中有一个TRUE(VERDADEIRO) 或FALSE(FALSO)，用于返回前缀是否相等。（抱歉图片是葡萄牙语）

我想到的公式是：

=EXACT(LEFT(SUBSTITUTE(A1;"_";"\";2);SEARCH("\";SUBSTITUTE(A1;"_";"\";2))-1);LEFT(SUBSTITUTE(B1;"_";"\";2);SEARCH("\";SUBSTITUTE(B1;"_";"\";2))-1))

解释：

=EXACT(str1,str2)

它比较字符串 str1 和 str2 是否相等。我们必须从 A 列和 B 列中提取前缀。为此，我们使用：

=LEFT(text, [num_chars])

它[num_chars]从字符串中提取字符text。要知道必须提取多少个字符，我们_用虚拟字符替换第二个下划线\：

=SUBSTITUTE(text, old_text, new_text, [instance])
=SUBSTITUTE(A1;"_";"\";2)

这里的技巧是可选参数[instance]。我们将其设置为 2，以替换下划线字符的第二次出现。

=SEARCH("\";SUBSTITUTE(A1;"_";"\";2))-1)

通过这个公式，我们找到了虚拟字符的位置\。把所有这些结合起来，我们就得到了上面的公式。

如果我们分步分解这个公式，我们会得到： 在此处输入图片描述

答案2

我读你的问题是为了知道 B 列中每个字符串的前缀是否可以在 A 列的任何位置找到。所以这个答案解决了这个问题。

我无法在一行中完成它（因为它必须查找 A 列中的所有位置）。但它只需要一个额外的列。

首先，从任何字符串中提取前缀的公式：

=LEFT(Column_A, FIND(Delimiter, Column_A, 1 + FIND(Delimiter, Column_A)) - 1)

  • Delimiter是“_”字符
  • 内部调用会找到第一个下划线的位置。然后，它会使用该位置作为第二个下划线位置的FIND()起点。FIND
  • 该LEFT()函数返回从字符串左侧开始的字符数。因此，我们1从第二个下划线的位置减去该字符数，得到字符串的前缀。

因此，您可以使用它来获取 A 列中每个前缀的列表。然后使用数组公式检查 B 列中的每个前缀是否存在于 A 列前缀列表中。

{=OR(LEFT(Column_B, FIND(Delimiter, Column_B, 1 + FIND(Delimiter, Column_B)) - 1)=Prefix_A)}

  • Prefix_A是 A 列前缀的完整列表。
  • 这与提取 A 列前缀的公式相同
  • 这需要是一个数组公式，因为函数内的语句OR()返回一个数组TRUE和FALSE值

这是我为回答此问题而构建的电子表格的屏幕截图： 检查 B 列前缀是否存在于 A 列中

我使用 FormulaChop 来生成这些示例公式（完整披露：我编写了 FormulaChop）。这里这是第一个公式的 FormulaChop 输出的屏幕截图。这里是我为回答这个问题而创建的电子表格的链接。

相关内容