Find and Count instances of a character in a string

Re: Find and Count instances of a character in a string

Just a thought to get you started:

let a variable run through the string, if the character it evaluates is not a ",", you delete it. Afterwards, use LEN() function.

Wigi

Re: Find and Count instances of a character in a string

If the string is in cell A1, use:
=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))

Re: Find and Count instances of a character in a string

Where are you doing this?

In VBA or on a worksheet?

x = "1,2"
    
    MsgBox Len(x) - Len(Replace(x, ",", ""))

Re: Find and Count instances of a character in a string

So simple... Should've known this.

Re: Find and Count instances of a character in a string

Whoa, this just blew my mind.

Turned a 30+ second operation on 16,000 cells with cell length 1000-1200 characters and searching for an 11-character string into a <1 second operation!

For a = 1 To Range("A" & Rows.Count).End(xlUp).Row
        If InStr(Range("A" & a).Value, "MtlChgLoc") = 69 Then
            Range("B" & a).Value = (Len(Range("A" & a).Value) - Len(Replace(Range("A" & a).Value, "Mtl EqMtlId", ""))) / 11 
        End If
    Next a

Awesome code...

Re: Find and Count instances of a character in a string

Hello all,

I realise that the conversation is more than a year old. However, I hope this will not be an issue.

So, genuine question: would anyone know whether the next lines of code are likely to run faster than the proposed solutions?

Sub main()
  MsgBox "There are " & countSeparators("A,B,C,D", ",") & " separators"
End Sub


Function countSeparators(myString as String, mySeparator as String) as Integer
   countSeparators = UBound(Split(myString, mySeparator))
End Function

I haven't tried it out yet.

Thanks,
Fred

Re: Find and Count instances of a character in a string

I find the following to work well, but I have not tested it for performance/memory impacts:

Dim Ruler As Variant
    Dim Counter As Long
    
    Ruler = Split("A,B,C,D", ",")
    Counter = UBound(Ruler)    
    MsgBox Counter

The split function returns a variant array (zero based). I don't use the contents of the array, just use it as a ruler and check the size of it.

Re: Find and Count instances of a character in a string

Very elegant. And... massively useful for zapping a disparate range of cells! Thanks!

Sub ZapWorksheetCells()
Dim arrCells() As String
Dim intN As Integer




arrCells = Split("b5,g5,i5,a8,a10,l8,l9,l10,o8,o9,o10,q8,q9,q10,a14,c17:m17,c18:q28,a22:a28,a32:q38,a41,f45:f46,s45,s46,t45,t46,a53:q60,a64:k71,a80:k97,d104:o115", ",")




For intN = 0 To UBound(arrCells)
    
    Range(arrCells(intN)).Select




    'do some stuff




Next intN




End Sub

Re: Find and Count instances of a character in a string

Quote from Fred_;574641

Hello all,

I realise that the conversation is more than a year old. However, I hope this will not be an issue.

So, genuine question: would anyone know whether the next lines of code are likely to run faster than the proposed solutions?

Code

Sub main()
  MsgBox "There are " & countSeparators("A,B,C,D", ",") & " separators"
End Sub
     
Function countSeparators(myString as String, mySeparator as String) as Integer
   countSeparators = UBound(Split(myString, mySeparator))
End Function

I haven't tried it out yet.

Thanks,
Fred

Display More

hi, thanks for that answer, was looking for test count character string quantity, in a cell. if not changed much? but am novice at vb and took me awhile to get.. this worked for me / so no one has to repeat it:
(mostly changed to read: count any character string).

Sub xcountCHARtestb()
    'If countCHAR(RANGE("aq528"), ".") > 0 Then    'YES
    If countCHAR(Selection, ".") > 0 Then   'YES
MsgBox "YES" & Space(10), vbQuestion ', "title"
Else
MsgBox "NO" & Space(10), vbQuestion  ', "title"
End If
End Sub
   
  
Sub xcountCHARtesta()   'YES
    MsgBox "There are " & countCHAR(Selection, "test") & " repetitions of the character string", vbQuestion  'YES
End Sub


Function countCHAR(myString As String, myCHAR As String) As Integer   'as: If countCHAR(Selection, "Your Test characters") > 1 Then  selection OR RANGE("aq528") '"any char string"
    countCHAR = UBound(split(myString, myCHAR))   'YES
End Function

Here are the performance data, for the sake of completeness. First, the character-counting code:

Private Function CharCount( _
    ByVal strText As String, _
    ByVal strFind As String, _
    Optional ByVal intMethod As Integer = 1 _
) As Integer

Dim avnt As Variant
Dim iint As Integer
Dim intReturn As Integer

intReturn = 0

Select Case intMethod
    Case 1    ''' Iterate through string
        For iint = 1 To Len(strText)
            If Mid(strText, iint, 1) = strFind Then intReturn = intReturn + 1
        Next
    Case 2    ''' Replace character and compare sizes
        intReturn = Len(strText) - Len(Replace(strText, strFind, ""))
    Case 3    ''' Split string into array
        avnt = Split(strText, strFind)
        intReturn = UBound(avnt, 1)
End Select

CharCount = intReturn

End Function

In the code above, Case 1 iterates through each character in the string, incrementing a counter if the character matches strFind. Case 2 replaces each instance of strFind in strText and compares the length of the original strText with the altered version. Case 3 splits the string into an array, using strFind as the boundary character.

Here is the test harness (TimerStart, TimerStop, and ElapsedTime are my high-resolution timer functions and not included here for the sake of brevity; feel free to use your own):

Public Sub TestCharCount()

Const lngcIterationCnt As Long = 100000
Const strcText As String = "A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z"

Dim ilng As Long

TimerStart
For ilng = 1 To lngcIterationCnt
    CharCount strcText, ",", 1
Next
TimerStop
Debug.Print "Method 1: " & ElapsedTime

TimerStart
For ilng = 1 To lngcIterationCnt
    CharCount strcText, ",", 2
Next
TimerStop
Debug.Print "Method 2: " & ElapsedTime

TimerStart
For ilng = 1 To lngcIterationCnt
    CharCount strcText, ",", 3
Next
TimerStop
Debug.Print "Method 3: " & ElapsedTime

End Sub

An average of test results (in seconds) for 100,000 iterations for each counting method are:

Method 1: 0.585

Method 2: 0.4672

Method 3: 0.432

Method 3 is 7.5% faster than Method 2 and 26.2% faster than Method 1. Your individual test results will vary, but the relationships between each method should hold.

It appears that the fastest way to count the instances of a given character in a string is to use the SPLIT and UBOUND functions (Method 3).