Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: Count Occurrences Of Text/Character Within a Cell

  1. #1
    Join Date
    4th July 2006
    Posts
    4

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310

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

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

    Cheers
    Andy


  3. #3
    Join Date
    4th July 2006
    Posts
    4

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

    VB:
    If LEN(A1)-LEN(SUBSTITUTE(A1,"/","")) >1 Then 
        Exit For 
    End If 
    
    


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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,337

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

    Hi,

    May be..

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

  5. #5
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,713

    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?

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

  6. #6
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310

    Re: Count Occurences Of Text Within a Cell

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

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

    Cheers
    Andy


  7. #7
    Join Date
    4th July 2006
    Posts
    4

    Re: Count Occurences Of Text Within a Cell

    Cool, that worked, thank you

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    4th July 2006
    Posts
    4

    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

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    10th February 2003
    Location
    Palm Beach, Florida
    Posts
    1,590

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

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

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

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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Count Occurrences Of Specific Text
    By Sonya Parker in forum EXCEL HELP
    Replies: 3
    Last Post: May 14th, 2008, 19:04
  2. How to count the occurrences of a number or text in a range in Excel ...
    By Dave Hawley in forum Excel Formulas (No Questions)
    Replies: 0
    Last Post: April 11th, 2008, 14:05

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno