Working with strings is a fundamental aspect of programming, and being able to count occurrences of specific characters or substrings within a string is a useful skill. In this article, we’ll explore different approaches to using VBA (Visual Basic for Applications) to count occurrences in a string, providing examples and explanations along the way.
How to Launch VBA Editor in Excel
- Press Alt + F11 to open your Microsoft Visual Basic.
- Select Insert and click on Module to open a blank module.
Method 1 – Counting the Number of Occurrences of a Word in a Given String
Let’s say we want to count how many times the word Excel appears in a string. We’ll use VBA to achieve this.
- Open the VBA Editor in Excel:
- Press Alt + F11 to open the Microsoft Visual Basic for Applications editor.
- Click Insert and select Module to create a new blank module.
- Enter the VBA Code:
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
Code Breakdown:
- 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.
- Run the Code:
- Press F5 to execute the code and see the output.
Method 2 – Counting the Number of Occurrences of a Word in Multiple Strings
Building onthe previous method, if you need to split text data into different cells and count how many times the word “Exceldemy” appears in each cell, the following code can assist 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
Code Breakdown:
- For Each cell In Range(“B5:B9”): This loop iterates through each cell in the range B5:B9 using a For Each loop.
- pos = InStr(1, cell.Value, “Exceldemy”: This line uses the InStr function to find the position of the substring “Exceldemy” within 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.
- Press the F5 key and see the output below.
Method 3 – Counting the Number of Occurrences of a Slash in a String Using a User-Defined Function
Suppose we have a dataset that shows different football players’ names along with their playing positions. Now, you want to count the number of times each playing position appears. For this case, you can create a user-defined function in VBA to achieve this.
- Create the User-Defined Function:
- Open your VBA Editor.
- Copy and paste the following code:
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
- Use the User-Defined Formula:
- Enter the following formula in cell D5 and press Enter:
=Occurrence_Count(C5)
- Drag the fill handle from D5 to D13 to get the results for other cells.
Method 4 – Counting Occurrences of a Character in a String Using VBA
In this method, we’ll input a string through an input box and count the occurrence of a specific character within it. Follow these steps:
- Create the VBA Subroutine:
- 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
Code Breakdown:
- 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.
- Run the Code:
- Press F5 to execute the code.
- An input box will prompt you to enter a string and the character you want to count.
- The code will display a message box with the result.
Download Practice Workbook
You can download the practice workbook from here:
Frequently Asked Questions
How to Count Strings in VBA?
There are multiple ways to count the number of strings in VBA. Here are some possible methods:
- Counting Elements in an Array of Strings:
- You can use the
UBound
function to determine the number of elements in an array of strings.
- You can use the
- Counting Characters in a String:
- To count the total number of characters in a string, you can utilize the built-in
LEN
function. It returns the length of the string, which corresponds to the number of characters.
- To count the total number of characters in a string, you can utilize the built-in
- Counting Occurrences of a Substring:
- If you need to count how many times a specific substring appears within a string, you can use the
InStr
andInStrRev
functions in a loop. These functions help locate occurrences of the substring.
- If you need to count how many times a specific substring appears within a string, you can use the
How to Count Characters in VBA?
In VBA, you can determine the number of characters in a string using the LEN
function. Here’s an example code snippet that counts the characters in a string variable called “Hello World”:
Dim myString As String
Dim count As Long
myString = "Hello World"
count = Len(myString)
After executing this code, the variable count will contain the total number of characters in the string “Hello World.”
<< Go Back to Count Words | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!