How To Count Specific Word That Occurs In a Cell?

by

Excel’s count functions are a powerful tool for analyzing data, but they have one limitation: they can only count values in individual cells. So, what do you do if you have all the values in one single cell?

In this tutorial, we’ll explore some options for solving this problem and show you how to use the LEN and SUBSTITUTE functions to count the number of occurrences of a specific value in a single cell.

Step 1: Understand the Problem

The problem we’re trying to solve is that you have all the values in one single cell and want to calculate the number of occurrences of a specific value. For example, let’s say you have the following values in cell A2: “John, Matthew, Jane, John, John”. You want to know how often the name “John” appears in this cell. The count function won’t work because the names are all in one cell.

Step 2: Use the LEN and SUBSTITUTE Functions

To solve this problem, we’ll use the LEN and SUBSTITUTE functions. The LEN function returns the length of a text string, and the SUBSTITUTE function replaces one text value with another. Let’s add a helper value first in the B2 cell, which will help us with the functions.

Here’s how to use the LEN and SUBSTITUTE functions.

  1. Enter the formula =LEN(A2) in a new cell (where A2 contains the data). This will give you the total number of characters in the cell.
  2. In the next cell, enter the formula =LEN(SUBSTITUTE(A2,B2,””)). This will give us the total number of non-john characters in the cell.
  3. In the other cell, enter the formula =(LEN(A2) – LEN(SUBSTITUTE(A2,B2,””)))/LEN(B2). This will give us the total number of John occurrences.

Done! To find the total number of occurrences of “John” in other cells, simply select the range in the function as we did in the screenshot.

Conclusion

Counting the number of occurrences of a specific value in a single cell can be a challenging task in Excel, but by using the LEN and SUBSTITUTE functions, it can be done with ease. By combining these two functions, you can calculate the number of occurrences of a specific value in a single cell. Thanks for reading!

Related Posts: