Count number of specific characters in a cell
This tutorial shows how to count the total number of specific characters in a cell through the use of an Excel formula or VBA
Example: Count number of specific characters in a cell
|
=LEN(B5)-LEN(SUBSTITUTE(B5,C5,""))
|
The formula uses a combination of the Excel LEN and SUBSTITUTE functions to return the number of times the letter "a" is captured in a specific cell (B5). The formula initially calculates the number of characters in a cell, 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 a cell.
|
Sub Count_number_of_specific_characters_in_a_cell()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'count the total number of specific characters in a cell
ws.Range("D5") = Len(ws.Range("B5")) - Len(Application.WorksheetFunction.Substitute(ws.Range("B5"), ws.Range("C5"), ""))
ws.Range("D5") = Len(ws.Range("B5")) - Len(Application.WorksheetFunction.Substitute(ws.Range("B5"), ws.Range("C5"), ""))
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("D5") in the VBA code.
Cell Reference: Select the cell from which you want to count the number of times a specific character is repeated in it by changing the cell reference ("B5") 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 cell by changing the cell reference ("C5") in the VBA code.
Worksheet Selection: Select the worksheet which captures the cell 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 ("D5") in the VBA code.
Cell Reference: Select the cell from which you want to count the number of times a specific character is repeated in it by changing the cell reference ("B5") 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 cell by changing the cell reference ("C5") in the VBA code.
Worksheet Selection: Select the worksheet which captures the cell 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 cell through the use of an Excel formula or VBA.
This tutorial shows how to count the total number of specific characters in a cell through the use of an Excel formula or VBA.
This tutorial provides one Excel method which uses the LEN and SUBSTITUTE functions, to count the number of specific characters in a cell. It initially calculates the total number of characters in a cell and then remove the number of characters in a cell, without the specific character.
The VBA method uses the Len and Substitute functions, similar to the Excel method, to count the number of specific characters in a cell.
FORMULA
=LEN(text)-LEN(SUBSTITUTE(text,count_for,""))
=LEN(text)-LEN(SUBSTITUTE(text,count_for,""))
ARGUMENTS
text: The string from which to extract the characters.
count_for: The character(s) that are to be counted from the selected text.
text: The string from which to extract the characters.
count_for: The character(s) that are to be counted from the selected text.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Count number of specific characters in a range | How to total number of specific characters in a range 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 |
|---|---|---|
| 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 |