User Defined Function for Sub-strings count from Excel Cell

We often need the count of sub-strings from out text available in Excel. For example we have comma separated ticket number in a cell and we have to count how many ticket numbers are available in the cell. We have semicolon “;” separated email ids the how can get the count of email ids.

Here we have given the solution of this problem with a User defined function in VBA.

Table of Contents

Below is the VBA code:

Copy the below given code and go to Visual Basic Editor (Press Alt+F11)
Insert a module (Press Alt+I+M)
Paste this code in the module.
Save as the workbook as Macro enable workbook.

Option Explicit

Function Sub_String_Count(str As String, Separator As String)

Application.Volatile

If Len(str) = 0 Then
    Sub_String_Count = 0
Else
    Sub_String_Count = (Len(str) - Len(Replace(str, Separator, ""))) / Len(Separator)
    Sub_String_Count = Sub_String_Count + 1
End If

End Function