I have a list of keywords and want to count the number of match keywords in each cells text from EXCEL File

Question

I have 2 sheet in a excel file. one is a Dictionary sheet and 2nd is a sheet containing a column of text. I want to match the dictionary keywords columns one by one and then the number of match keywords counts in each cell of the text column.

I have tried these formulas: =(LEN(B2)-LEN(SUBSTITUTE(B2,Sheet1!A:A,"")))/LEN(Sheet1!A:A) in this B2 is the first (start) cell of the text column and Sheet1!A:A is the dictionary column of other sheet. but by this i get zero as a result

=(LEN(B2)-LEN(SUBSTITUTE(B2,Sheet1!A:A,"")))/LEN(Sheet1!A:A)

The result will be like this:

         Text                      number_of_keyword_match    | number_of_keyword_match using DIC col 2 |  ........
                                        using DIC col 1

1 any Text or sentence/sentences          e.g match "3"             
2                                                    7              
3                                                    0              
4                                                    15             
5    .................................................                                   
7 .....................................................
.......................................................
..................................continue up to 2815 rows....

Expected inputs and output aren't entirely clear. I've tried answering what I assumed was your meaning, but please elaborate on in- and outputs if you meant something else. — Harm te Molder

Harm te Molder Harm te Molder · Accepted Answer · 2019-02-05T09:03:38

Assuming your text input looks like this:

 |          A           |
-+----------------------+
1|apple apple beat beat |
2|apple beat beat carrot|
3|carrot apple apple    |

and your dictionary looks like this:

 |   A   |   B   |
-+-------+-------+
1|apple  |beat   |
2|beat   |carrot |
3|       |       |

This formula will give you the count per word per cell of text

=(LEN(text!A1)-LEN(SUBSTITUTE(text!A1,dictionary!A1,"")))/LEN(dictionary!A1)

(In this example 2)

If I understand correctly, your expected output would be extra columns in the text sheet where each cell contains the sum of counts of each word in the corresponding column in dictionary, right? For example:

 |          A           | B | C |
-+----------------------+---+---+
1|apple apple beat beat | 4 | 2 |
2|apple beat beat carrot| 3 | 3 |
3|carrot apple apple    | 2 | 1 |

You can use array formulas to do this, starting with this one in cell B1:

=SUM(IFERROR((LEN(text!$A1)-LEN(SUBSTITUTE(text!$A1;dictionary!A:A;"")))/LEN(dictionary!A:A);0))

But instead of pressing Enter after pasting it in, press Ctrl+Shift+Enter to run it as an array formula. Then drag this formula down and to the right to get all the counts you want.

I have a list of keywords and want to count the number of match keywords in each cells text from EXCEL File

2 Answers