Determine whether a range of cells contains a substring of another cell-CodePudding

I want to determine whether a range of cells contains a substring of another cell. In this concrete example, for every cell in Column B individually, I want to find out whether any value of Column A is a substring of the cell.

Column A	Column B	Column C
42:	123: Lorem ipsum	Yes
123	42: dolor sit amet	Yes
456	test 123: At vero eos	Yes
987	Foo Bar	No

I tried to look up existing solutions but could only find solutions to a similar problem: "Find out whether "42:" is a substring of any of Column B. I am not trying to find a substring in a set of strings. I want to search a set and find values that are substrings of a specific value

CodePudding user response：

You can use:

Formula in C1:

=SUM(--ISNUMBER(FIND(" "&SUBSTITUTE(A$1:A$4,":",)&" "," "&SUBSTITUTE(B1,":",)&" ")))>0

Or change SUM() with SUMPRODUCT() in older versions of Excel.

Note that the above would assume that the only other character allowed other than digits are colons. And they could be anywhere in the string e.g.: 1:2:3:::4:. It's up to you to decide wheather or not that is too likely.