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?
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
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!
