Computing how many times a word appears in a text in Excel

In this post, I would like to remind myself on how to compute how many times a word appears in a text, in case I forget.

Suppose I have the following text in a cell in Excel:

“Here is the perfect system for cleaning your room. First, move all of the items that do not have a proper place to the center of the room. Get rid of at least five things that you have not used within the last year. Take out all of the trash, and place all of the dirty dishes in the kitchen sink. Now find a location for each of the items you had placed in the center of the room. For any remaining items, see if you can squeeze them in under your bed or stuff them into the back of your closet.”

And suppose we want to compute how often the word “room” appears in these text. Or perhaps, we want to compute how many times the letter “a” appears in the text. We could compute it manually of course, but let’s compute it the smart way.

The command to do this in Excel is not difficult. Let’s suppose the text above is stored in Cell A2 and suppose the word “room” is placed in Cell A3. Put your cursor in Cell A4 and type the following:

“=(LEN(A2)-LEN(SUBSTITUTE(A2,B2,””)))/LEN(B2)”

That’s it. This simple of code is enough to compute how often the word “room” appears in the text.

Final word, this could be useful for people who wants to learn the distribution of each letter appearing in an English sentence.