UDF for Sub String Count
VBA

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)

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

 

Click here to download Excel template.

 

Watch the step by step 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

Leave a Reply

Your email address will not be published. Required fields are marked *