Find and Count instances of a character in a string

  • I want to count the number of commas in a cell, if any.


    tells me the location of the first comma (2), but I want the total number of commas to be returned (7). I've been using a laborious character-by-character check from 1 to len(string) and incrementing the variable CommaCount every time it finds a comma, but I'm pretty sure there's an easier way.

  • 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.




    Excel MVP 2011-2014

    For more Excel memes: visit ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • 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.


  • 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!

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

    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).

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

    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):

    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).

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!