☰

如何在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 檢查該列是否在另一列中來做到這一點。但我一直在尋找 oneliner 功能。

答案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值組成的陣列

這是我為回答這個問題而建立的電子表格的螢幕截圖： 檢查A列中是否有B列前綴

我使用 FormulaChop 產生這些範例公式（全面披露：我編寫了 FormulaChop）。這裡是第一個公式的 FormulaChop 輸出的螢幕截圖。這裡是我為回答這個問題而建立的電子表格的連結。

相關內容