Working with strings is a fundamental aspect of programming, and being able to count occurrences of a specific character or substring within a string is a useful skill to have. specific patterns in text. In this article, we will explore different approaches to using VBA to Count Occurrences in String, providing examples and explanations along the way.
How to Launch VBA Editor in Excel
To open the VBA code editor in Excel you can utilize the keyboard shortcut. Let’s see the process.
- Press Alt + F11 to open your Microsoft Visual Basic.
- Then press Insert > Module to open a blank module.
VBA to Count Occurrences in String: 4 Different Cases in Excel
Counting occurrences in a string is important and hard to deny their necessity in a practical context. For instance, If you are building an application that requires a search functionality, counting the occurrences of a search term in a string can be essential. This will help you provide users with relevant search results. In this article, we will explore four different scenarios to count occurrences in a string.
1. Count Number of Occurrences of Word in a Given String
In this example, we will take a string from the worksheet and count how many times the “Excel” word appears.
Sub Word_Occurrences_Count() 'Set the string to search and the substring to find myString = Cells(5, 2).Value searchSubstring = "Excel" 'Loop through the string, counting occurrences of the substring count = 0 pos = InStr(1, myString, searchSubstring) Do While pos > 0 count = count + 1 pos = InStr(pos + 1, myString, searchSubstring) Loop 'Display the result MsgBox "The substring '" & searchSubstring & "' appears " & count & " times in the string." End Sub
- myString = Cells(5, 2).Value: This line sets the variable myString to the value of cell B5.
- searchSubstring = “Excel”: This line sets the variable searchSubstring to the string “Excel“.
- pos = InStr(1, myString, searchSubstring): This line finds the position of the first occurrence of searchSubstring within myString. If no occurrence is found, pos is set to 0.
- Do While pos > 0: This line begins a loop that will continue as long as a match for searchSubstring is found.
- count = count + 1: This line increments the count variable to record the occurrence of searchSubstring.
- pos = InStr(pos + 1, myString, searchSubstring): This line finds the position of the next occurrence of searchSubstring within myString, starting from the position after the previous match. If no further match is found, pos is set to 0.
- MsgBox “The substring ‘” & searchSubstring & “‘ appears ” & count & ” times in the string.”: This line displays a message box with the number of occurrences of searchSubstring found in myString.
Now press on the F5 key and see the output as given below.
2. Counting Number of Occurrences of Word in Multiple String
Following the previous example, if you want to split some text data into different cells and count how many times the “Exceldemy“ appears in each cell, then this code might aid you.
Sub Occurrence_Count_Multiple_String() For Each cell In Range("B5:B9") count = 0 pos = InStr(1, cell.Value, "Exceldemy") Do While pos > 0 count = count + 1 pos = InStr(pos + 1, cell.Value, "cat") Loop MsgBox "The substring 'Exceldemy' appears " & count & " times in cell " & cell.Address & "." Next cell End Sub
- For Each cell In Range(“B5:B9”): loops through each cell in the range B5:B9 using a For Each loop.
- pos = InStr(1, cell.Value, “Exceldemy”: uses the InStr function to find the position of the substring “Exceldemy” in the cell value. If the substring is found, the code enters a Do While loop.
- The Do While loop increases the count variable by 1 for each occurrence of the substring in the cell value.
- The Do While loop continues searching for occurrences of the substring in the cell value until no more occurrences are found.
- The code displays a message box for each cell, showing the number of occurrences of the substring “Exceldemy” in that cell. The message box includes the cell address.
All good! Hit the F5 key and see your output below.
3. Number of Occurrences of Slash from a String Through User-Defined Function
Suppose we have the dataset given above that shows different football players’ names along with their playing position. Now you want to count their playing role. For this case, you can use the following code to make a user-defined function and count the playing position later.
- To begin with, copy the following code in your VBA Editor.
Function Occurrence_Count(istring As String) iResult = Len(istring) - Len(Replace(istring, "/", "")) Occurrence_Count = iResult + 1 End Function
The given code defines a VBA function named Occurrence_Count that takes a string argument istring and returns the count of occurrences of a specific character (“/” in this case) in the given string.
- Write the following user-defined formula in the D5 cell and press enter afterward.
- Drag the Fill Handle from D5 to D13 to get the other result.
4. Count Occurrences of Character in String with VBA
In the last example, we will input a string through the input box and count the occurrence of a specific character within it. To do so, copy and paste the following code into your VBA editor.
Sub Character_Count_in_String() myString = InputBox("Drop a String Here") subString = InputBox("Which Character you would Like to Count") count = 0 pos = InStr(1, myString, subString) Do While pos > 0 count = count + 1 pos = InStr(pos + 1, myString, subString) Loop If count >= 1 Then MsgBox "The substring '" & subString & "' appears " & count & " times in the " & Chr(39) & myString & Chr(39) Else MsgBox " There is no '" & subString & "' in " & Chr(39) & myString & Chr(39) End If End Sub
- The Sub statement indicates the start of a subroutine called Character_Count_in_String.
- The InputBox function takes a string (myString) and a character to search for (subString).
- pos = InStr(1, myString, subString): search for the subString in myString, starting at the first character (position 1). If the subString is found, it returns the position of the first occurrence of the subString to the pos variable or returns 0 otherwise.
- A Do While loop is used to continue searching for the subString in myString until it is no longer found. If pos is greater than 0 (i.e. if the subString is found), then the count variable is incremented by 1 and the pos variable is set to the position of the next occurrence of the subString.
- If count is greater than or equal to 1, a message box is displayed indicating how many times the subString was found in the myString. If count is 0, a message box is displayed indicating that the subString was not found in the myString.
- The End Sub statement indicates the end of the subroutine.
Now press on the F5 key and see the output as given below.
Frequently Asked Questions
- How do you count strings in VBA?
There are multiple ways to count the number of strings in VBA. Some of the possible ways are listed below.
- Count the number of elements in an array of strings using the UBound function.
- Count the number of characters in a string using the LEN function.
- Count the number of occurrences of a substring in a string using the InStr and InStrRev functions in a loop.
- How do you count characters in VBA?
In VBA, you can count the number of characters in a string using the built-in LEN function. The LEN function returns the length of a string, which is the number of characters in the string.
Here’s an example code that counts the number of characters in a string variable called “Hello World“.
Dim myString As String Dim count As Long count = Len(Hello World)
After this code is executed, the variable count will contain the number of characters in the string “Hello World“.
Download Practice Workbook
You can download and practice the dataset that we have used to prepare this article.
In this article, we have listed and tried to give some practical examples of VBA to count occurrences in a string. As you have already understood, there is plenty of ways to do this task. However, If you have any queries, feel free to comment below and we will get back to you soon.