Count characters in a string (Not Len)

[bkelly]

Is there a way to count the number of times a particular character is found in a string. I tried to treat the string as an array and VBA would not let me. I could use the Mid function on one character at a time and do a compare, but that seems kind of gross.

Is there any other way?

 

[erik.van.geit]

Hi,,
can you use this ?

Sub test()
Dim txt As String
Dim char As String

txt = "abanbbba"
char = "a"
MsgBox Len(txt) - Len(Application.Substitute(txt, char, ""))

End Sub

kind regards,
Erik

 

[bkelly]

Hello Erik,

I ran a few test cases and it works just fine. I never would have come up with that one.

Problem Solved, Thank you.

 

[northwolves]

"Split" is another choice.

Let's use Mr. Erik 's example:

Sub test()
Dim txt As String
Dim char As String
txt = "abanbbba"
char = "a"
MsgBox UBound(Split(txt, char))
End Sub

Best Regards