Count number of specific characters in a range
This tutorial shows how to count the total number of specific characters in a range through the use of an Excel formula or VBA
Example: Count number of specific characters in a range
|
=SUMPRODUCT(LEN(B5:B7))-SUMPRODUCT(LEN(SUBSTITUTE(B5:B7,D5,""))))
|
The formula uses a combination of the Excel SUMPRODUCT, LEN and SUBSTITUTE functions to return the number of times the letter "a" is captured in a range (B5:B7). The formula initially calculates the number of characters in each cell within the specified range, through the use of the Excel LEN function. The second part of the formula calculates the number of characters without the specific character, which in this case is "a", by using the LEN and SUBSTITUTE functions. The formula then subtracts the second part from the first part of the formula to return the number of times the specific character ("a") is presented in each cell. It then uses the SUMPRODUCT function to sum the results from each cell in the range.
|
Sub Count_number_of_specific_characters_in_a_range()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'count the total number of specific characters in a range
ws.Range("E5").Formula = "=SUMPRODUCT(LEN(B5:B7))-SUMPRODUCT(LEN(SUBSTITUTE(B5:B7,D5,"""")))"
ws.Range("E5").Formula = "=SUMPRODUCT(LEN(B5:B7))-SUMPRODUCT(LEN(SUBSTITUTE(B5:B7,D5,"""")))"
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("E5") in the VBA code.
Range: Select the range from which you want to count the number of times a specific character is repeated in it by changing the range reference ("B5:B7") in the VBA code.
Characters to Count for: Select the cell that holds the character(s) that you want to count for in a specific range by changing the cell reference ("D5") in the VBA code.
Worksheet Selection: Select the worksheet that captures the range from which you want to count the number of times a specific character is captured by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Output Range: Select the output range by changing the cell reference ("E5") in the VBA code.
Range: Select the range from which you want to count the number of times a specific character is repeated in it by changing the range reference ("B5:B7") in the VBA code.
Characters to Count for: Select the cell that holds the character(s) that you want to count for in a specific range by changing the cell reference ("D5") in the VBA code.
Worksheet Selection: Select the worksheet that captures the range from which you want to count the number of times a specific character is captured by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
METHOD 2. Count number of specific characters in a range using VBA with a For Loop
VBA
Sub Count_number_of_specific_characters_in_a_range()
'declare variables
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
countchars = 0
For x = 5 To 7
countchars = countchars + Len(Application.WorksheetFunction.Substitute(Range("B" & x).Value, ws.Range("D5"), ""))
Next x
For x = 5 To 7
countchars = countchars + Len(Application.WorksheetFunction.Substitute(Range("B" & x).Value, ws.Range("D5"), ""))
Next x
totalchar = 0
For x = 5 To 7
totalchar = totalchar + Len(Range("B" & x).Value)
Next x
For x = 5 To 7
totalchar = totalchar + Len(Range("B" & x).Value)
Next x
ws.Range("E5") = totalchar - countchars
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("E5") in the VBA code.
Range: The column and row range references are separately classified in this example. The column reference is represented by "B", given that the data range is captured in column B and the row numbers are represented by the values assigned to x, which are between 5 and 7.
Characters to Count for: Select the cell that captures the character(s) that you want to count by changing the cell reference ("D5") in the VBA code.
Worksheet Selection: Select the worksheet that captures the range from which you want to count the number of times a specific character is captured by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Output Range: Select the output range by changing the cell reference ("E5") in the VBA code.
Range: The column and row range references are separately classified in this example. The column reference is represented by "B", given that the data range is captured in column B and the row numbers are represented by the values assigned to x, which are between 5 and 7.
Characters to Count for: Select the cell that captures the character(s) that you want to count by changing the cell reference ("D5") in the VBA code.
Worksheet Selection: Select the worksheet that captures the range from which you want to count the number of times a specific character is captured by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
EXPLANATION
This tutorial shows how to count the total number of specific characters in a range through the use of an Excel formula or VBA.
This tutorial shows how to count the total number of specific characters in a range through the use of an Excel formula or VBA.
This tutorial provides one Excel method which uses the SUMPRODUCT, LEN and SUBSTITUTE functions, to count the number of specific characters in a range. It initially calculates the total number of characters in each cell and then removes the number of characters from each cell, without the specific character.
This tutorial provides two VBA method. The first method uses the VBA formula function with the same formula that was used in the Excel method. The second method uses the Len and Substitute functions to count the number of specific characters in a cell and loops through each cell in the specified range. Whilst looping through each cell it accumulates the result from each cell to return the total number of specific characters in a range.
FORMULA
=SUMPRODUCT(LEN(range))-SUMPRODUCT(LEN(SUBSTITUTE(range,count_for,"")))
=SUMPRODUCT(LEN(range))-SUMPRODUCT(LEN(SUBSTITUTE(range,count_for,"")))
ARGUMENTS
range: Range of cells that contain the characters that you want to count.
count_for: The characters that are to be counted from the selected range.
range: Range of cells that contain the characters that you want to count.
count_for: The characters that are to be counted from the selected range.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Count number of specific characters in a cell | How to total number of specific characters in a cell using Excel and VBA | |
| Count duplicate values | How to count duplicate values in a range using Excel and VBA | |
| Count number of cells in a range | How to count the total number of cells in a range using Excel and VBA methods | |
| Count number of characters in a cell excluding spaces | How to count the total number of characters in a cell, excluding spaces, using Excel and VBA | |
| Count numeric cells in a range | How to count cells that contain numeric values in a specific range using Excel and VBA |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| SUMPRODUCT Function | The Excel SUMPRODUCT function multiplies corresponding ranges and returns the sum of these values | |
| 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 |