In Excel, how can I create the "intersection" of two strings (without dropping into VB)?

Question

In Mac Excel 2011, I have two strings, each consisting of a space-separated concatenation of smaller, spaceless strings. For example:

"red green blue pink" "horse apple red monkey pink"

From those, I'd like to extract the intersection string:

"red pink"

I can do it in VB, but I'd prefer to stay in Excel proper. Now I know I could hack something together (in Excel) by making an assumption about the number of smaller component strings within each larger string. I could then chop one of the larger strings into those components and then for each do a FIND() on the second large string, concatenating the result as I went.

Problem is, although here I'm giving only two strings, in practice I have two sets of strings, each containing 20 large strings. So the "chop and walk" approach feels like O(N^2) in terms of space in Excel, and I'm looking for a simpler way.

Any ideas?

Answer 1

I don't think you can do it in a single cell function without using multiple cells or VBA. Define a UDF like the one below and use the new function in the one cell with the syntax

=StringIntersect("a b c","d e b f")

which would return "b"

This function does have the nested loop but on string arrays I imagine it will be quick enough

Function StringIntersect(s1 As String, s2 As String) As String
Dim arys1()  As String
Dim arys2() As String
Dim arysub() As String
Dim i as integer
Dim j as integer

arys1 = Split(s1, " ")
arys2 = Split(s2, " ")
For i = LBound(arys1) To UBound(arys1)
    For j = LBound(arys2) To UBound(arys2)
        If arys1(i) = arys2(j) Then StringIntersect = StringIntersect & arys1(i) & " "
    Next
Next
StringIntersect = Trim(StringIntersect) 'remove trailing space
End Function

If you don't want to do to the two loops you should be able to do something with inStr which is very quick. I haven't done any speed testing but I suspect the function below is quicker, however you will get unexpected results is the string is duplicated in the first input or the string in the first input is a substring in the second. This could be avoided with more checking but you would probably loose the speed benefit.

Function StringIntersect(s1 As String, s2 As String) As String
Dim arys1()  As String

arys1 = Split(s1, " ")
For i = LBound(arys1) To UBound(arys1)
    If InStr(1, s2, arys1(i), vbBinaryCompare) > 0 Then StringIntersect = StringIntersect & arys1(i) & " "
Next
StringIntersect = Trim(StringIntersect) 'remove trailing space

End Function