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

count occurrence of character vba excel

Objective

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

Approach

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.

Code

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

Reference 

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/instr-function