Announcement

Collapse
No announcement yet.

Count Occurrences Of Text/Character Within a Cell

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

  • Count Occurrences Of Text/Character Within a Cell



    Hi,

    I'm trying to count the number of occurnaces of a character within a cell to use as criteria within an If statement in a Macro.... For example

    Hi/This is/ Randy
    Hi/this is Randy

    I'd like to count the number of "/" within the cell 2 for the first, 1 for the second.

  • #2
    Re: Count the number of Occurances within a Cell - VBA

    Try this
    =LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))

    Cheers
    Andy

    Comment


    • #3
      Re: Count the number of Occurances within a Cell - VBA

      Hmm.. Unless I'm missing something I don't appear to be able to use Substitue within a Visual Basic Macro to use this? Otherwise I think it would work. I.e...

      Code:
      	IF LEN(A1)-LEN(SUBSTITUTE(A1,"/","")) >1 then
        Exit for
      End if


      Hope this makes sense.
      Last edited by Dave Hawley; August 23rd, 2006, 12:44.

      Comment


      • #4
        Re: Count the number of Occurances within a Cell - VBA

        Hi,

        May be..

        If Len([a1]) - Len(Replace([a1].Value, "/", "")) > 1 Then
        Exit For
        End If
        Kris

        ExcelFox

        Comment


        • #5
          Re: Count the number of Occurances within a Cell - VBA

          PLEASE use the code tags for any code as you agreed. I think you should read the rules you have agreed to, don't you?

          Code:
          Sub CountChar()
              MsgBox Len(Range("A1")) - Len(Replace(Range("A1"), "/", ""))
          End Sub

          Comment


          • #6
            Re: Count Occurences Of Text Within a Cell

            Sorry rtgleck, did read the in Macro part of the question.

            LEN(range("A1"))-LEN(application.WorksheetFunction.SUBSTITUTE(range("A1"),"/",""))

            Cheers
            Andy

            Comment


            • #7
              Re: Count Occurences Of Text Within a Cell

              Cool, that worked, thank you

              Comment


              • #8
                Re: Count the number of Occurances within a Cell - VBA

                My apologies Dave, I guess i didn't completly understand what was being asked for there. I'll make sure I do in the future. Thank you

                Comment


                • #9


                  Re: Count the number of Occurances within a Cell - VBA

                  Originally posted by rtgleck
                  Hmm.. Unless I'm missing something I don't appear to be able to use Substitue within a Visual Basic Macro to use this? Otherwise I think it would work. I.e...

                  Code:
                  	IF LEN(A1)-LEN(SUBSTITUTE(A1,"/","")) >1 then
                    Exit for
                  End if


                  Hope this makes sense.
                  Well... actually you can use it in VBA the same way you'd use it in a worksheet cell. That is, if you wanted to...

                  Code:
                  Sub test()
                      x = [Len(A1) - Len(Substitute(A1, "/", ""))]
                      If x > 1 Then
                          MsgBox x
                      End If
                  End Sub
                  Sub All_Macros(Optional control As Variant)

                  Comment

                  Working...
                  X