How to Count the Number of Times a Character / Word Appears in a Cell? (2024)

Excel is an effective spreadsheet programme that provides a variety of tools and capabilities to modify and analyse data. When working with sizable datasets or performing data analysis, knowing how to count the instances of a particular character or word within a cell can be helpful. Regardless of your level of Excel proficiency, this lesson will walk you through each step of the process so that you can do this assignment quickly. So let's get started and discover how to determine how many times a character or word appears in an Excel cell!

Count the Number of Times a Character / Word Appears

Here, we can complete the task directly using a single formula. So let us see a simple process to know how you can count the number of times a character or word appears in a cell in Excel.

Step 1

Consider an Excel sheet where the data in the sheet is similar to the below image.

First, to count the number of times a word appeared, click on an empty cell, in this case cell B2, and enter the formula as

=(LEN(A2)-LEN(SUBSTITUTE(A2, "thoughts","")))/LEN("thoughts") and click enter. In the formula, A2 is the cell and thoughts is the word we are counting.

Empty cell > Formula > Enter.

Step 2

Now, if you want to count the occurrence of a character, replace the word in the formula with a character. That is, use the formula =(LEN(A2)-LEN(SUBSTITUTE(A2, "r","")))/LEN("r") and click enter.

Empty cell > Formula > Enter.

Note

The formulas are case-sensitive. If you want to count the characters or words without case-sensitivity, use the formula as

=(LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),UPPER("remove"),"")))/LEN("remove"). This is how you can count the number of times a character or word appears in a cell in Excel.

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can count the number of times a character or word appears in a cell in Excel.