Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

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

  1. #1
    Join Date
    7th September 2005
    Posts
    72

    Find and Count instances of a character in a string

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

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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,068

    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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035

    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.

  4. #4
    Join Date
    14th July 2004
    Posts
    10,541

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

    Where are you doing this?

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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,068

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

    So simple... Should've known this.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    27th July 2010
    Posts
    1

    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!

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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    12th May 2011
    Posts
    1

    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?

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

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    15th August 2012
    Posts
    1

    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:

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

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    5th August 2013
    Posts
    2

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

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

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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Count Number Of Instances In A Column
    By paulk84 in forum EXCEL HELP
    Replies: 9
    Last Post: August 20th, 2013, 14:45
  2. How to find a character in a string
    By Pacheco in forum EXCEL HELP
    Replies: 5
    Last Post: April 5th, 2012, 01:52
  3. Count Number Of Instances By Month
    By Remedial in forum EXCEL HELP
    Replies: 7
    Last Post: August 6th, 2008, 07:45
  4. Analyzing a string character by character
    By Alex Feature in forum EXCEL HELP
    Replies: 1
    Last Post: May 23rd, 2005, 23:32

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