How to count or find instance of a character in a string in VBA Excel -debugvba

count occurrence of character vba excel


To count the instance of a character in a string using VBA Excel.


In the code below we have created 2 variables, there is one variable SourceString which contains a source string which we want to scan for occurrence of a particular character, while there is another variable called SearchString which contains the character, here we aim to find the occurrence of search character in the source string.

Here we have used the InStr function to find if the character exists in the source string, additionally we have looped through the source string to find the occurrence of a character, we have used the counter variable to count how many times a particular character has appeared in a string.

Here we have used vbBinaryCompare in the InStr function, which makes the matching process as case sensitive, we can also use vbTextCompare instead of vbBinaryCompare, vbTextCompare makes the matching process non case sensitive.


Sub FindCharacterOccurence()

Dim SourceString As String
Dim SearchString As String

'This is the string in which we shall search any character
SourceString = "ThisIsMyTestString"

'This is the character we shall search in the search string menationed above
SearchString = "t"

StrLen = Len(SourceString)

For StartPos = 1 To StrLen

StringPosition = InStr(StartPos, SourceString, SearchString, vbBinaryCompare)
'Exit the loop if string was not found
If StringPosition = 0 Then

'Print the character occurence
MsgBox "Search character '" & SearchString & "' appeared " & Count & " times"
Exit Sub
End If
Count = Count + 1

'Reset the search position if string is found
StartPos = StringPosition

Next StartPos

End Sub


In the image below we can see that we have showcased the output in a message box.


count occurrence of character vba excel



We can also get the input from excel cells and apply the similar logic there and store the output in a column.


Post you may like

How to find column letter in cell address using VBA Excel