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
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:
Visit our YouTube channel to learn step-by-step video tutorials
