To acquire the number of some specific keyword in a cell, SEARCH function for that specific keyword can be used to find it and make a count using some other support functions such as ISNUMBER and SUMPRODUCT functions.
Count Keywords Cell Contains
To count how many times a specific keyword is used in a cell. Following formula can be used in general. The detail of working of this formula is explained below.
The above formula can be used to find the number of times a keyword is used in a cell. This formula incorporates 3 functions i.e. SUMPRODUCT, ISNUMBER, and SEARCH.
SEARCH function is used to search for a specific keyword in the selected cell “Xn” or some range of cells. If the specific keyword is found in any cell e.g. A1. It returns the position of the first character of the keyword found in that specific text. If the word is not found, it generates a #VALUE! error.
The ISNUMBER function returns TRUE the argument passed to it is a number, if not it returns FALSE.
The double negative sign is used to change the array of TRUE and FALSE entries into ones and zeros respectively.
Finally, SUMPRODUCT function is used to calculate the sum of the entries of the array in it, that returns the number of times the keyword has occurred in the text.
Few sample sentences are considered in the form of an Excel sheet. Along with that, a separate array of keywords is formed, which contains a list of keywords that are to be found in the sentences.
Figure1. Sample sheet for extracting all partial matches from the data
Specify the keywords in the keywords array and to get the number of keywords to enter the following formula in the cell C3. And read the cell B3 in the SEARCH function.
Figure2. Getting the count value for the matched results from the data
Now according to the formula, each sentence is checked for the 3 keywords separately and the corresponding count is provided in front of the sentence.