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
Supports Office/Excel 2007-2021 and 365 | Available in 44 Languages | Easy to Uninstall Completely
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away...
Supercharge Your Excel Skills: Experience Efficiency Like Never Before with Kutools for Excel (Full-Featured 30-Day Free Trial)
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! (Full-Featured 30-Day Free Trial)
