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-

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: