Welcome! Register Now to Unlock Exclusive Excel TutorialsGet Started
- Exclusive Playlists
- Downloadable Videos
- No Ads!
- Exclusive Templates
Description: In this video, I will show you how to count specific words that occur in a cell. This is different from counting cells because we can not just use the COUNT function and specify "count" or "cells." We need to look at each word individually and then add it up. To do this we will use the LEN and SUBSTITUTE functions. These two functions allow us to find out how many characters are in a text string; and if we want, we can replace them with something else too!
Table Of Content:
01:08 Adding Helper Value
02:22 Adding Length of the cell using LEN Function
03:10 Substituting Value with Blank Value using LEN & SUBSTITUTE Function
04:16 Calculating the number of John in Specific Cell
04:57 Complete formula to Count Specific Word Occurs in a Cell
Count function has its own limitation as you cannot count specific words that occur in a cell. So
here we are using Excel LEN and SUBSTITUTE functions to resolve the same. Let's take an example where we want to count a number of "John" in a specific cell.
We will first add a helper value so that we can easily identify the location of John in the text string. This is done by simply adding an apostrophe (') at the beginning and end of the text string as shown below:
Now, we can use the LEN function to find out how many characters are in this text string. The LEN function will return 11 as there are 11 characters including both apostrophes.
Next, we will use the SUBSTITUTE function to replace all instances of John.
So we are using two functions here, lets see the argument of both.
=SUBSTITUTE(text, search_term, replace_with)
Now follow the video above to understand how to do the same.