counting the number of occurences of a character in string

[tryer]

Can anyone help with a formula to count the number of times a particular character is found in a string I have been experimenting with the likes of

count = instr(teststring,count(";"))

but obviously this doesnt work, VBA help doesn't say much about the count function

[DonkeyOte]

I wouldn't use Count as a variable name given it's a function... perhaps lngCount (Long), eg:

Dim lngCount As Long
Dim strTest As String
strTest = "Hello;Hello;Hello"
lngCount = Len(strTest) - Len(Replace(strTest,";",""))

[tryer]

Thanks DonkeyOte works perfectly

[Chris Bode]

Probably this will help you out,

    Dim cnt As Integer
    Dim str As String
    str = "dsada,dsad,dasdsada,dsada,"
    Dim i As Integer
    cnt = 0
    For i = 1 To Len(str)
        If Mid(str, i, 1) = "," Then
            cnt = cnt + 1
        End If
    Next
 
    MsgBox cnt.

Here cnt contains the number of occurance of character “,”…

Hope you get it

Have a nice time



Chris

[Leith Ross]

Hello Tryer,

Here is another method that doesn't change the original string. It also performs a non case sensitive search. It can be used to search for a single character or multiple characters.

Sub Macro1()
 
  Dim Cnt As Long
  Dim S As String
  
    S = "ABDaCdEFIoPxAZZQa"
    Cnt = UBound(Split(S, "a", , vbTextCompare))
    
End Sub

[tryer]

Thnaks guys, lots of different ways to achieve the same result