Find how many words from cell are found in an array

Question

I have two columns with data. The first one has some terms and the other one contains single words. what I have

I'm looking for a way to identify which words from each cell from the first column appear in the second, so the result should look something like this (I don't need the commas): what I need

My question is somehow similar to Excel find cells from range where search value is within the cell but not exactly, because I need to identify which words are appearing in the second column and there can be more than one word.

I also tried =INDEX($D$2:$D$7;MATCH(1=1;INDEX(ISNUMBER(SEARCH($D$2:$D$7;A2));0);)) but it also returns only one word.

score 0 · Answer 1 · answered Jan 30 '17 at 10:41

I have a partial solution:

=IF(1-ISERROR(SEARCH(" "&D2:D7&" "," "&A2&" ")),D2:D7&", ","")

This formula returns an array of the words contained in the cell (ranges are according to your picture). This array is sparse: it contains empty strings for each missing word. And it assumes that words are always separated by one space (this may be improved if necessary).

However, native Excel functions are not capable of concatenating an array, so I think the rest is not possible with native formulas only.

You would need VBA but if you use VBA you should not bother with the first part at all, since you can do anything.

score 0 · Accepted Answer · answered Jan 30 '17 at 11:52

If you are willing to use VBA, then you can define a user defined function:

Public Function SearchForWords(strTerm As String, rngWords As Range) As String
    Dim cstrDelimiter As String: cstrDelimiter = Chr(1) ' A rarely used character
    strTerm = cstrDelimiter & Replace(strTerm, " ", cstrDelimiter) & cstrDelimiter ' replace any other possible delimiter here
    SearchForWords = vbNullString
    Dim varWords As Variant: varWords = rngWords.Value
    Dim i As Long: For i = LBound(varWords, 1) To UBound(varWords, 1)
        Dim j As Long: For j = LBound(varWords, 2) To UBound(varWords, 2)
            If InStr(1, strTerm, cstrDelimiter & varWords(i, j) & cstrDelimiter) <> 0 Then
                SearchForWords = SearchForWords & varWords(i, j) & ", "
            End If
        Next j
    Next i
    Dim iLeft As Long: iLeft = Len(SearchForWords) - 2
    If 0 < iLeft Then
        SearchForWords = Left(SearchForWords, Len(SearchForWords) - 2)
    End If
End Function

And you can use it from the Excel table like this:

=SearchForWords(A2;$D$2:$D$7)

that's exactly what I needed, it works great! thank you so much! — Paula, Jan 30 '17 at 13:25

Gordon · Answer 3 · 2017-01-30T12:03:32.100

You can create a table with the words you want to find across the top and use a formula populate the cells below each word if it's found. See screenshot.

[edit] I've noticed that it's incorrectly picking up "board" in "blackboard" but that should be easily fixed.

=IFERROR(IF(FIND(C$1,$A2,1)>0,C$1 & ", "),"")

Simply concatinate the results

=CONCATENATE(C2,D2,E2,F2,G2,H2)

or

=LEFT(CONCATENATE(C2,D2,E2,F2,G2,H2),LEN(CONCATENATE(C2,D2,E2,F2,G2,H2))-2)

to take off the last comma and space

I've edited this to fix the problem with "blackboard"

new formula for C2

=IF(OR(C$1=$A2,ISNUMBER(SEARCH(" "&C$1&" ",$A2,1)),C$1 & " "=LEFT($A2,LEN(C$1)+1)," " & C$1=RIGHT($A2,LEN(C$1)+1)),C$1 & ", ","")

New formula for B2 to catch the error if there are no words

=IFERROR(LEFT(CONCATENATE(C2,D2,E2,F2,G2,H2,I2),LEN(CONCATENATE(C2,D2,E2,F2,G2,H2,I2))-2),"")

Find how many words from cell are found in an array

3 Answers3