excel - Excel函数在另一个单元格中查找任何给定的列值作为子字符串

Question

如何在 excel 或 sql 中找出 excel 列的任何成员单元格是否是另一个 excel 列单元格值的子字符串，并将其作为函数输出放在相邻单元格中？

我还尝试运行 SQL 查询（HP Quality Center 数据库），尝试了多个内置公式的 excel，但我从未得到正确的结果。

这是我使用的 SQL 代码片段，它没有返回正确的结果。

WITH FH_HEIRARCHY (AL_DESCRIPTION, AL_ITEM_ID, AL_FATHER_ID, PATH) AS

(

SELECT
 A.AL_DESCRIPTION /*Test Plan Folder.Name*/,
 A.AL_ITEM_ID /*Test Plan Folder.Item Id*/,
 A.AL_FATHER_ID /*Test Plan Folder.Parent Folder*/,
 CAST(A.AL_DESCRIPTION AS varchar(1000)) AS PATH

 FROM ALL_LISTS A /*Test Plan Folder*/

 WHERE A.AL_FATHER_ID = 0

 UNION ALL

 SELECT

 B.AL_DESCRIPTION /*Test Plan Folder.Name*/,
 B.AL_ITEM_ID /*Test Plan Folder.Item Id*/,
 B.AL_FATHER_ID /*Test Plan Folder.Parent Folder*/,
 CAST(F.PATH + '\' + B.AL_DESCRIPTION AS varchar(1000)) AS PATH

 FROM ALL_LISTS B /*Test Plan Folder*/

 INNER JOIN FH_HEIRARCHY F ON B.AL_FATHER_ID = F.AL_ITEM_ID
)

SELECT

 TS.TS_TEST_ID,
 TS.TS_NAME,
 TS.TS_STEPS,
 TS.TS_EXEC_STATUS,
 TS.TS_DESCRIPTION,
 TS.TS_USER_02,
 DS.DS_ID, /*Design Step.Step ID*/
 DS.DS_STEP_NAME, /*Design Step.Step Name*/
 DS.DS_DESCRIPTION, /*Design Step.Step Description*/
 DS.DS_EXPECTED,
 DS.DS_USER_01, /*Design Step.Tcode*/
 FH.AL_ITEM_ID AS FOLDER_ID /*Test Plan Folder.Item Id*/,
 FH.AL_DESCRIPTION AS FOLDER_NAME /*Test Plan Folder.Name*/,
 FH.PATH AS FOLDER_PATH /*Test Plan Folder.Path*/

FROM TEST TS /*Test*/

 LEFT OUTER JOIN FH_HEIRARCHY FH /*Test*/

  ON FH.AL_ITEM_ID = TS.TS_SUBJECT /*CTE*/

 LEFT OUTER JOIN DESSTEPS DS /*Design Step*/

  ON DS.DS_TEST_ID = TS.TS_TEST_ID

WHERE TS.TS_NAME IS NOT NULL AND FH.PATH LIKE '%MASTER%TESTS%' AND (

DS.DS_DESCRIPTION Like ('%' +         ' /SAPAPO/AC03 '         + '%')
OR DS.DS_DESCRIPTION Like ('%' +         ' /SAPAPO/C3 '         + '%')
);

基本上，如果列 B 中的任何值存在于列 A 的给定单元格值作为子字符串，我想在列 C 中输入 true，否则为 false。

C

olumn A    | Column B    | Column C

Hi there    | Hi          | TRUE

Hello there | Ho          | FALSE

标签： excel

Answer 1

Excel公式：

=ISNUMBER(SEARCH(B1,A1))

---

如果需要完整的单词匹配，例如Hiis 不匹配，Hillside那么我们可以使用：

=ISNUMBER(SEARCH(" " & B1 & " "," " & A1 & " "))

---

SEARCH不区分大小写。如果需要区分大小写，请替换SEARCH为FIND

---

要查看 A1 中的 B 中是否有任何匹配，请使用 SUMPRODUCT 包装器：

=SUMPRODUCT(--(ISNUMBER(SEARCH(" "&$B$1:$B$2&" "," "&A1&" "))))>0