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