Announcement

Collapse
No announcement yet.

Find and Count instances of a character in a string

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Find and Count instances of a character in a string



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

    Code:
    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.

  • #2
    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) --

    Comment


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

      If the string is in cell A1, use:
      =LEN(A1)-LEN(SUBSTITUTE(A1,",",""))
      Best Regards,
      Tom
      ---------------------------
      Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

      Comment


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

        Where are you doing this?

        In VBA or on a worksheet?
        Code:
            x = "1,2"
            
            MsgBox Len(x) - Len(Replace(x, ",", ""))
        Boo!

        Comment


        • #5
          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) --

          Comment


          • #6
            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:
                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...

            Comment


            • #7
              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:
              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

              Comment


              • #8
                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:
                    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.

                Comment


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

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

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

                  Comment


                  • #10


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

                    Originally posted by Fred_ View Post
                    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

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

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

                    Comment

                    Working...
                    X