Count number of specific substrings in a cell
This tutorial shows how to count the number of specific substrings (which can be a specific word or number) in a cell using Excel formulas or VBA
Example: Count number of specific substrings in a cell
|
=(LEN(B7)-LEN(SUBSTITUTE(B7,$C$4,"")))/LEN($C$4)
|
This formula uses the Excel LEN and SUBSTITUTE functions to count the number of times the word excel is present in a specific cell. This formula is case sensitive.
|
METHOD 2. Count number of specific substrings in a cell (case insensitive) using Excel formula
EXCEL
Sub Count_number_of_specific_substrings()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'count number of specific substrings in a cell
ws.Range("D5").Formula = "=(LEN(B7)-LEN(SUBSTITUTE(B7,$C$4,"""")))/LEN($C$4)"
ws.Range("D5").Formula = "=(LEN(B7)-LEN(SUBSTITUTE(B7,$C$4,"""")))/LEN($C$4)"
End Sub
Sub Count_number_of_specific_substrings()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'count number of specific substrings in a cell
ws.Range("D5").Formula = "=(LEN(B7)-LEN(SUBSTITUTE(UPPER(B7),UPPER($C$4),"""")))/LEN($C$4)"
ws.Range("D5").Formula = "=(LEN(B7)-LEN(SUBSTITUTE(UPPER(B7),UPPER($C$4),"""")))/LEN($C$4)"
End Sub
EXPLANATION
This tutorial shows how to count the number of specific substrings in a cell using Excel formulas or VBA.
This tutorial provides two Excel and VBA methods that can be used to count the number of specific substrings in a cell.
The first method is case sensitive and the second is case insensitive.
The first method is case sensitive and the second is case insensitive.
FORMULA (case sensitive)
=(LEN(string)-LEN(SUBSTITUTE(string,substring,"")))/LEN(substring)
=(LEN(string)-LEN(SUBSTITUTE(string,substring,"")))/LEN(substring)
FORMULA (case insensitive)
=(LEN(string)-LEN(SUBSTITUTE(UPPER(string),UPPER(substring),"""")))/LEN(substring)
=(LEN(string)-LEN(SUBSTITUTE(UPPER(string),UPPER(substring),"""")))/LEN(substring)
ARGUMENTS
string: The string from which to count the number of substrings.
substring: The substring to count for.
string: The string from which to count the number of substrings.
substring: The substring to count for.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Return substring | How to extract a substring with a nominated start and end position using Excel and VBA |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| LEN Function | The Excel LEN function returns the number of characters in a specified string | |
| SUBSTITUTE Function | The Excel SUBSTITUTE function replaces characters with another in a specified string | |
| UPPER Function | The Excel UPPER function converts all lowercase text in a specified text string to uppercase |