Web Analytics India

- Contributed by Deep Moni Hazarika

Counting the length of a string or characters is easily done in excel. Just the LEN() function and simple. But the one day I was asked if there was any way to count a particular word in a cell which had a long sentence. That sounded tricky but needed some fact finding. So the problem was to find a word that occurs repeatedly in a sentence which happens to be in a cell in an excel sheet. Though I don’t know of any direct function or method to calculate out that, I was able to find out the solution with a combination of functions.
Considering the cell with the sentence is B3, the combined function to use is
=(LEN(B3)-LEN(SUBSTITUTE(B3,”Word_To_Count”,”")))/Length_Of_The_Word_To_Count
e.g. Suppose we want to know how many times the word “subject” appears in the sentence “subject matter of the subject in the name of the subject” in cell B3 of your workbook, the formula will be
=(LEN(B3)-LEN(SUBSTITUTE(B3,”subject”,”")))/7
Now let’s try to understand the function. The first use of LEN() calculates the length of the complete string. In the second use of LEN(), we first use SUBSTITUTE() with the word “subject” to be replaced by ‘null’. In the process, SUBSTITUTE() will result in the sentence removing the word “subject”. LEN() is then used to calculate the length of this reduced string. The difference of these is the total length of missing words “subject” and this result divided by the number of character in “subject” itself will result in the number of times the word appeared in the sentence. Isnt that tricky?
If you know of any other shorter or smarter method, do comment.

If you need a sample report on any of these dashboard tricks, please leave your email id in a comment. And we will mail it over to you.