UDF for Sub String Count

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.

User Defined Function
User Defined Function


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

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)


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

End Function


Click here to download Excel template.


Watch the step by step tutorial:

My Name is PK. I am founder of PK-AnExcelExpert.com. I am a Microsoft Certified Professional. I have more than 12 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.

Leave a Reply