Find and Count instances of a character in a string

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


    Code
    1. instr("A,B,C,D",",")

    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.


    Wigi

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> 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


    So simple... Should've known this.

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> 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!


    Code
    1. For a = 1 To Range("A" & Rows.Count).End(xlUp).Row
    2. If InStr(Range("A" & a).Value, "MtlChgLoc") = 69 Then
    3. Range("B" & a).Value = (Len(Range("A" & a).Value) - Len(Replace(Range("A" & a).Value, "Mtl EqMtlId", ""))) / 11
    4. End If
    5. 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?


    Code
    1. Sub main()
    2. MsgBox "There are " & countSeparators("A,B,C,D", ",") & " separators"
    3. End Sub
    4. Function countSeparators(myString as String, mySeparator as String) as Integer
    5. countSeparators = UBound(Split(myString, mySeparator))
    6. 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:


    Code
    1. Dim Ruler As Variant
    2. Dim Counter As Long
    3. Ruler = Split("A,B,C,D", ",")
    4. Counter = UBound(Ruler)
    5. 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).