In this Chapter, We will learn how to count specific word occurs in a cell in your worksheet. Previously we have learned how to count a number of values using OR criteria, AND Criteria and we also have done how to count errors that occurs in your worksheet using COUNT Functions but COUNT Function cannot be used in Count Specific Word occurs in a cell.
COUNT Functions are limited to counting particular values in single cells but cannot count specific word occurs the same cell. For example, we can count the number of Johns in different cells but we cannot do the same when that specific word i.e. John occurs multiple times in a single cell. Therefore, To resolve this we need to calculate using LEN, SUBSTITUTE Functions as we have used in the video above or you can also follow the tutorial below.
Following are the values we have for this chapters explanation;
As you can see we have a number of johns that occurs in A2, A3 cells. Therefore, we cannot Count Specific Word using the count functions. However, we can do the same with power query, but we won’t be using that since this chapter is all about formula and functions, Therefore, we will use the helper functions i.e. LEN and SUBSTITUTE functions.
If you want to do the practice with us please be assure to download the practice sheet using the link below;
First, we will add the helper value i.e. “John” in B2 cell in order to find the number of Johns in A2 and A3. You may be asking yourself why?, It is because it will be easier for us to do the matching, subtracting the value from A2 which you will learn in later stages.
In this Chapter, I wont jump straight to the formula, but, I will explain each steps of our formula in different steps then we will collaborate the whole formula in one.
STEP1 – Find Total Length of A2
In cell C2, Input the LEN formula to find the total length of cell A2;
Now select A2 cell;
Now close the parenthesis and click ENTER and here we got the value 42 which includes the spaces, words, symbols etc. Therefore, if you keep adding spaces the length will also increase.
STEP2 – Substitute The Value of Specific Word with Blank Value
Now in next cell, D2, What we need to do is, we will be substituting the value of John with the blank value using SUBSTITUTE Function and then returns the length of cell using LEN Function.
In C2, Input the LEN Function first;
Now, Type the Substitute function and here we get the three arguments i.e. text, old_text, new_text;
In the first argument of SUBSTITUTE Function i.e. text, we need to enter the text reference cell from which our value (John) needs to be substituted i.e. A2. ;
In second argument i.e. old_text, We need to add the cell reference of value which needs to be replaced or substituted (John in our case) i.e. B2;
In third and last argument i.e. new_text, we will add the new value which should be put in place of John which will be blank and we will write as “” to exclude any space to counted;
Now we will close the parenthesis and click ENTER, we get the value i.e. 22;
STEP3 – Subtract Total Length With Calculated Length
In the next step, we will subtract the total length i.e. 42 in C2 with the non-john length in D2 i.e. 22 to find the length of all John’s in cell A2.
Now close the parenthesis and click ENTER, It returns the 20 value which is the total lenght of John occurs in a cell. Now we will divide the total lenght of Johns with the lenght of Single John i.e. 4 in E2, Therefore our formula will become =(C2-D2)/4 ;
Now click ENTER, Here we get the value 5.
And 5 is the number of John in A2;
STEP4 – Count Specific Word in SINGLE Formula
Now we have learned everything step-by-step, If we want to put everything in one formula in one cell, we can do that by following;
LEN(A2) – Total Length of the cell A2
LEN(SUBSTITUTE(A2,B2,””)) – Length of all Non-Johns in A2.
LEN(B2) – Length of John
Therefore, In simple Words;
Number of John = (Total Length of cell – Length of Non-John)/Length of John
STEP5 – Count Specific Word In Ranges
We can see there two more john in cell A3, therefore to count specific word in range we can do that by editing the current formula by adding A2:A3 in total number of length i.e. LEN(A2:A3);
Also, One more in SUBSTITUTE Function i.e. SUBSTITUTE(A2:A3,B2,””) to count specific word in a range and click ENTER;
Here we got the value 2. Now we will wrap all formula by adding SUMPRODUCT function in start to calculate all values of John in ranges;
Now close the parenthesis and click ENTER, Here you get the total which is 7.
Therefore, Here we concluded this chapter i.e. Count Specific Word occurs in cell or ranges in excel.
If you still have questions, do shoot in the comment box below.