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;

Count Specific Word Occurs in a Cell in 4 Easy Steps 20

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;

Download Practice Sheet

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.

Count Specific Word Occurs in a Cell in 4 Easy Steps 21

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;

Count Specific Word Occurs in a Cell in 4 Easy Steps 22

Now select A2 cell;

Count Specific Word Occurs in a Cell in 4 Easy Steps 23

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.

Count Specific Word Occurs in a Cell in 4 Easy Steps 24

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;

Count Specific Word Occurs in a Cell in 4 Easy Steps 25

Now, Type the Substitute function and here we get the three arguments i.e. text, old_text, new_text;

Count Specific Word Occurs in a Cell in 4 Easy Steps 26

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. ;

Count Specific Word Occurs in a Cell in 4 Easy Steps 27

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;

Count Specific Word Occurs in a Cell in 4 Easy Steps 28

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;

Count Specific Word Occurs in a Cell in 4 Easy Steps 29

Now we will close the parenthesis and click ENTER, we get the value i.e. 22;

Count Specific Word Occurs in a Cell in 4 Easy Steps 30

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.

Count Specific Word Occurs in a Cell in 4 Easy Steps 31

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 ;

Count Specific Word Occurs in a Cell in 4 Easy Steps 32

Now click ENTER, Here we get the value 5.

Count Specific Word Occurs in a Cell in 4 Easy Steps 33

And 5 is the number of John in A2;

Count Specific Word Occurs in a Cell in 4 Easy Steps 34

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)-LEN(SUBSTITUTE(A2,B2,””)))/LEN(B2)

Whereas,

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

Count Specific Word

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);

Count Specific Word Occurs in a Cell in 4 Easy Steps 35

Also, One more in SUBSTITUTE Function i.e. SUBSTITUTE(A2:A3,B2,””) to count specific word in a range and click ENTER;

Count Specific Word Occurs in a Cell in 4 Easy Steps 36

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;

Count Specific Word Occurs in a Cell in 4 Easy Steps 37

Now close the parenthesis and click ENTER, Here you get the total which is 7.

Count Specific Word Occurs in a Cell in 4 Easy Steps 38

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.

Next: Count Blank Cells In Excel