Count number of keywords in a text string based on a list

[sdchris]

I am trying to figure out a way to quickly count how many keywords are in a cell, based on a list of keywords that I will have to add to/subtract from in the future. Here is an example:

Let's say I have text that reads the following:

"Alpha, bravo, charlie, delta, echo, foxtrot, golf, alpha, charlie"

Initially, I will only want to count the number of times "alpha" and "bravo" occur. This would be easy using IF(ISNUMBER(SEARCH...- this would return "3" because alpha occurs 2 times, and bravo once. In the future, I may want to add "charlie" to the list? Any thoughts/ideas on how to do this efficiently would be much appreciated.

Thanks,
Chris

 

[JoeMo]

Maybe there's an elegant worksheet functions combination to do this, but for me, I'd go with a UDF like this.
the list of key words in this example is in D1:D3 and the phrase is in A1. The UDF is below.
Sheet7

*	A	B	C	D
Alpha, bravo, charlie, delta, echo, foxtrot, golf, alpha, charlie	*	alpha
*	*	*	bravo
*	*	*	charlie

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B1 =CountKeyWords(A1,D1:D3) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Function CountKeyWords(R As Range, List As Range) As Long
Dim s As Variant, Ct As Long, i As Long, j As Long
s = Split(R, ",")
For i = LBound(s) To UBound(s)
    For j = 1 To List.Rows.Count
        If LCase(Trim(s(i))) = LCase(Trim(List.Cells(j, 1))) Then
            Ct = Ct + 1
        End If
    Next j
Next i
CountKeyWords = Ct
End Function

After installing the UDF you can use it by entering: =CountKeyWords(A1,D1:D3) in B1.
Note this assumes all words in the string are coma separated.

 

[Marcelo Branco]

A possible solution using a formula (keywords in D1:D3)


[TABLE="class: grid"]
<TBODY>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Alpha, bravo, charlie, delta, echo, foxtrot, golf, alpha, charlie​

[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD]

alpha​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

Alpha,alpha,bravo,bravo​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD]

bravo​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

Alpha​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

charlie​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

Bravo,charlie,bravo​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

Alpha,alpha​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

Blah,charlie,blah​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

Blah, blah​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


Formula in B1 copied down
=SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER($D$1:$D$3),"")))/LEN($D$1:$D$3))

M.