Word Count in Excel
VBA

UDF to get the frequency of specific word in a Paragraph

In this article, you will learn how to create a User Defined Function in VBA to get the frequency of specific word in a paragraph. Using this UDF you will be able to get the Total Words, Specific word count (Case Sensitive Check) and Specific word count (Non-Case Sensitive Check).

For example, we have below given paragraph, and we have got the Total word count and Specific word count also-

Word Count in Excel
Word Count in Excel

 

Below is the code for this user defined function –

Option Explicit

Function Word_Count(Complete_Text As String, Optional Specific_Word As String, Optional Case_Sensitive As Boolean)

Application.Volatile

If Len(Specific_Word) = 0 Then
Word_Count = (Len(Complete_Text) - Len(Application.WorksheetFunction.Substitute(Complete_Text, " ", ""))) + 1
Else
If Case_Sensitive Then
Word_Count = (Len(Complete_Text) - Len(Application.WorksheetFunction.Substitute(Complete_Text, Specific_Word, ""))) / Len(Specific_Word)
Else
Word_Count = (Len(Complete_Text) - Len(Application.WorksheetFunction.Substitute(UCase(Complete_Text), UCase(Specific_Word), ""))) / Len(Specific_Word)
End If
End If


End Function

Click here to download the Practice file-

Watch the step by step video tutorial:


 

PK
My Name is PK. I am founder of PK-AnExcelExpert.com. I am a Microsoft Certified Professional. I have more than 10 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.
https://www.pk-anexcelexpert.com