How to count number of certain/specific words in a cell or a range of cells in Excel?
Supposing you need to count the number of times a specific word appears in a cell or a range of cells, how can you achieve it? This tutorial provides two methods to help you solve this problem step by step.
Count number of certain words in a cell with formula
Quickly count number of certain words in a cell or range of cells with Kutools for Excel
Count number of certain words in a cell with formula
You can combine the LEN and SUBSTITUTE functions to count number of specific word in a cell in Excel.
=(LEN(text)-LEN(SUBSTITUTE(text,word,"")))/LEN(word)
1. Select a blank cell, enter the below formula into it and press the Enter key.
=(LEN(B2)-LEN(SUBSTITUTE(B2,D2,"")))/LEN(D2)
Notes:
Then you will see the total number of a certain word in a specific cell is calculated and populated in the selected cell.
Count number of certain words in a cell or range of cells with Kutools for Excel
The Count Times a word appears utility of Kutools for Excel helps you easily count number of certain words in a cell or a range of cells without remembering any formulas.
Before applying Kutools for Excel, please download and install it firstly.
1. Select a blank cell to output the result, then click Kutools > Formula Helper > Formula Helper.
2. In the Formulas Helper dialog box, please configure as follows..
Tips: You can check the Filter box, enter a keyword into the textbox to quickly filter the formula you need.
Tips: You can directly enter the certain word into the Word box as the below screenshot shown.
Then you will get the result in the selected cell. See screenshot:
Note: This feature is case sensitive.
If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps.
Quickly count number of certain words in a cell or range of cells with Kutools for Excel
Related articles:
- How to count the number of words in a cell or a range cells in Excel?
- How to count occurrences of specific character in a cell in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!