VB and VBA Users Source Code: Counting the number of instances of a string within another string
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Counting the number of instances of a string within another string
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Tuesday, June 17, 2003
Hits:
1067
Category:
Visual Basic General
Article:
The code below returns the number of instances of a string within another string. 'Purpose : Counts the number of instances of a specified string within another string. 'Inputs : sText The string to search in. ' sSearchFor The string to search for. ' [bIgnoreCase] If True does a case insensitive comparison. ' [sIgnoreText] If specified will ignore items between subsequent instances of ' "sSearchFor" which match this text (see example 2) 'Outputs : Returns the number of instances of the string. 'Author : Andrew Baker 'Date : 03/09/2000 13:58 'Example : eg. Find the instances of the character "A" within a string ' 1. ' Debug.Print StringCountInstances("ABCAA","A") ' Returns 3. ' Now find how many lines of data are contained within a string, ignoring any blanks lines. ' 2. ' Debug.Print StringCountInstances("ANDREW" & vbnewline & "BAKER" & vbnewline & vbnewline & "IS" & vbnewline & "GREAT!" & vbnewline,vbnewline,,"") ' Returns 3 (NOT 4). 'Revisions : Function StringCountInstances(sText As String, sSearchFor As String, Optional bIgnoreCase As Boolean = True, Optional sIgnoreText As String) As Long Dim asItems() As String, lThisItem As Long On Error GoTo ErrFailed If bIgnoreCase Then asItems = Split(UCase$(sText), UCase$(sSearchFor)) StringCountInstances = UBound(asItems) Else asItems = Split(sText, sSearchFor) StringCountInstances = UBound(asItems) End If If Len(sIgnoreText) Then 'Deduct any items which contain the specified "sIgnoreText" For lThisItem = 0 To UBound(asItems) - 1 If asItems(lThisItem) = sIgnoreText Then 'Deduct this item StringCountInstances = StringCountInstances - 1 End If Next End If Exit Function ErrFailed: 'Error occurred Debug.Print "Error in StringCountInstances " & Err.Description Debug.Assert False StringCountInstances = -1 End Function
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
