Excel Function to find any given column value as substring in another cell

Question

How do I find out in excel or sql if any member cell of an excel column is a substring of another excel column cell value, and put it as a function output in adjacent cell?

I also tried to run SQL query (HP Quality Center database), tried multiple excel built in formulas, but I never get correct result.

This is my SQL code snippet, I used which does not return correct results.

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 '         + '%')
);

Basically, I want to put in column C true if any value in column B is present for the given cell value of Column A as a substring, and false otherwise.

C

olumn A    | Column B    | Column C

Hi there    | Hi          | TRUE

Hello there | Ho          | FALSE

Answer 1

Excel Formula:

=ISNUMBER(SEARCH(B1,A1))

---

If complete word matches are wanted, for example Hi is does not match in Hillside then we can use:

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

---

SEARCH is Case in-sensitive. If case sensitivity is desired replace SEARCH with FIND

---

To see if any in B match in A1 then use SUMPRODUCT wrapper:

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