Yousuf Khan
Microsoft Excel is a powerful software that uses Excel functions to count almost anything. Unfortunately, Excel doesn’t come with a built-in feature for word counts. Fortunately, you can create simple formulas to do practically any task by combining different functions. And we’ll apply this strategy to Excel’s word count. With this in mind, in this article, we will start from scratch and show you 3 simple methods to count specific words in Excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try it yourself while you go through the process.
3 Suitable Ways to Count Specific Words in Excel
In this article, we will count certain words using the Excel formula, COUNTIF function, and VBA code in Excel. Moreover, we will determine the total number of occurrences for sensitive and insensitive cases. Therefore, we will need separate formulas for separate cases. To demonstrate, we take a dataset that represents the geographical locations and their zip codes.
1. Apply Excel Formula to Count Certain Words
You can use a formula based on the SUBSTITUTE, LEN, and SUMPRODUCT functions to determine how many times a certain word (or any substring) appears inside a group of cells in Excel. The SUBSTITUTE function under Excel text function category generally replaces a text with another text in a text string. Alternatively, the LEN function returns an integer that represents the length of a text. Further, the SUMPRODUCT function returns the sum of products of any corresponding arrays. Moreover, we will calculate the sum products of 2 cases namely, case-insensitive & case-sensitive.
1.1 Count Specific Words for Case-Insensitive
Since the SUBSTITUTE function is case-sensitive by default, as you are presumably aware, the word counting algorithm based on SUBSTITUTE is also case-sensitive. Follow the steps to implement.
Steps:
- First, in cell C12, we type New York as the input word.
- This is because we want to count how many occurrences of New York happen in the dataset.
- Afterward, in cell C13, type the formula,
- As a result, 3 appears as the return of the formula.
- Thus, the total number of New York occurrences is 3.
🔍 How Does the Formula Work?
- (SUBSTITUTE(B5:C10,C12,””): Here, the SUBSTITUTE function takes the substring (New York) out of the original text.
- (LEN(B5:C10)-LEN(SUBSTITUTE(B5:C10,C12,””)): while the LEN function determines the length of the text without the substring.
- SUMPRODUCT((LEN(B5:C10)-LEN(SUBSTITUTE(B5:C10,C12,””)))/LEN(C12)): Lastly, the SUMPRODUCT function adds up every element in the array to determine how many times the substring has appeared overall in the cell range.
1.2 Count Specific Words for Case-Sensitive
For case-sensitive, use the UPPER function in the LEN function to change the case of the original text and the text you wish to count if you need to count both uppercase and lowercase instances of a specific term. The formula produces the same word count whether the term is inputted in uppercase, lowercase, or sentence case, as shown in the screenshot below. Follow the steps given below.
Steps:
- To begin with, we input Park in cell C12.
- After that, in cell C13, write the following formula,
- Further, tap the Enter key.
- Hence, integer 3 pops up.
- Therefore, the word Park appears 3 times in the dataset.
Read More: How to Count Words in Excel (6 Easy Methods)
Similar Readings
- How to Show All Text in an Excel Cell (2 Easy Ways)
- [Fixed!] Excel Links Not Working Unless Source Workbook Is Open
- How to Fix Formula in Excel (9 Easy Methods)
- Move Data from Row to Column in Excel (4 Easy Ways)
- How to Insert WordArt in Excel (2 Simple Methods)
2. Use COUNTIF Function to Count Specific Words in Excel
In this method, we will use the COUNTIF function to count a certain word in our dataset. The COUNTIF function counts the list in a text string and returns an integer. Here, we will count the number of occurrences 1633 (zip code of New York) that appear in the dataset. To do so, follow the procedures carefully.
Steps:
- First, input 1633 in C12.
- See the below picture to understand better.
- Next, in the C13, type the following COUNTIF formula,
- Later, press Enter.
- Subsequently, the total count appears in the dataset.
- Therefore, the total count of 1633 in the dataset is 2.
Read More: How to Count Alphabet in Excel Sheet (5 Easy Methods)
3. Run Excel VBA Code to Count Specific Words in Excel
In our last method, we will apply VBA code to count the specific words in a range. We will apply the WorksheetFunction property and .Countif VBA function. Moreover, we will include the MsgBox function to display the result. Follow the steps.
Steps:
- First, we input the word New York in C12, since we want to count the total occurrences of it.
- Further, go to the Developer tab and click Visual Basic.
- Consequently, the Visual Basic window pops up.
- Next, navigate through the Insert tab and tap the Module box.
- Eventually, the module box appears.
- Later, type the VBA code in the box.
Sub Count_SWord()
Range("C13") = Application.WorksheetFunction.CountIf(Range("B5:C10"), Range("C12"))
MsgBox Range("C12") & " appears " & Range("C13") & " times."
End Sub
- Finally, hit the green Run button.
- Thus, the message box pops up and we obtain the desired result.
Read More: How to Search Text in Multiple Excel Files (2 Easy Ways)
Conclusion
In conclusion, we have discussed some easy steps to count specific words in Excel. Not to mention, our ExcelDemy website shows various simple problem-solving methods like this. Please leave any further queries or recommendations in the comment box below.
Related Articles
- Create Double Entry Bookkeeping in Excel
- How to Overline Text in Excel (4 Easy Ways)
- Do Bookkeeping for Small Business in Excel (Step-by-Step)
- How to Write a Paragraph in Excel Cell (5 Easy Ways)
- Add Signature in Excel (3 Quick Ways)
- Use of Task Pane in Excel (Detailed Analysis)
- How to Make a Box Plot in Excel (With Easy Steps)
