Find and Count instances of a character in a string

[cscribner]

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

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.

[Wigi]

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

[thomach]

If the string is in cell A1, use:
=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))

[norie]

Where are you doing this?

In VBA or on a worksheet?

x = "1,2" 
 
MsgBox Len(x) - Len(Replace(x, ",", "")) 

[Wigi]

So simple... Should've known this.

[theradpotato]

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!

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

[Fred_]

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?

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

[Grey5706]

I find the following to work well, but I have not tested it for performance/memory impacts:

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.