Welcome! Lets start your excel journey..

Welcome! Register Now to Unlock Exclusive Excel Tutorials

Get Started
  • Exclusive Playlists
  • Downloadable Videos
  • No Ads!
  • Exclusive Templates

How To Count Specific Word That Occurs In a Cell?

Admin
Duration: 7:41
Submitted: 1 month ago
Views: 64

Comments (0)

Link to this video:

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:

00:00 Introduction

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

07:32 Conclusion

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:

='John'

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.

=LEN(text)

=SUBSTITUTE(text, search_term, replace_with)

Now follow the video above to understand how to do the same.