Count numeric characters in a range
This tutorial shows how to count the total number of numeric characters in a range through the use of an Excel formula or VBA
Example: Count numeric characters in a range
|
=SUMPRODUCT(LEN(B5:B7)-LEN(SUBSTITUTE(B5:B7,{1,2,3,4,5,6,7,8,9,0},"")))
|
This formula uses a combination of the Excel SUMPRODUCT, SUBSTITUTE and LEN functions to return the total number of numeric characters (0-9) from a range of cells (B5:B7). In this example the formula will count numbers 12 from from cell B5, 34 from cell B6 and 5678 from cell B7, which amount to eight numeric characters from the three cells.
|
Sub Count_numeric_characters_in_a_range()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'apply formula to count the total number of numeric characters in a range
ws.Range("C5").Formula = "=SUMPRODUCT(LEN(B5:B7)-LEN(SUBSTITUTE(B5:B7,{1,2,3,4,5,6,7,8,9,0},"""")))"
ws.Range("C5").Formula = "=SUMPRODUCT(LEN(B5:B7)-LEN(SUBSTITUTE(B5:B7,{1,2,3,4,5,6,7,8,9,0},"""")))"
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("C5") in the VBA code.
Range to count from: Select the range from which you want to count for only numeric values by changing the range reference ("B5:B7") in the VBA code.
Worksheet Selection: Select the worksheet which captures the range of cells from which you want to count the number of numeric characters 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 ("C5") in the VBA code.
Range to count from: Select the range from which you want to count for only numeric values by changing the range reference ("B5:B7") in the VBA code.
Worksheet Selection: Select the worksheet which captures the range of cells from which you want to count the number of numeric characters 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 total number of numeric characters (0-9) in a range through the use of an Excel formula or VBA.
This tutorial one Excel method which uses the SUMPRODUCT, SUBSTITUTE and LEN functions, to count the number of numeric characters in a range of cells.
The VBA method uses the VBA formula function and the exact same formula that is provided in the Excel method.
FORMULA
=SUMPRODUCT(LEN(range)-LEN(SUBSTITUTE(range,{1,2,3,4,5,6,7,8,9,0},"")))
=SUMPRODUCT(LEN(range)-LEN(SUBSTITUTE(range,{1,2,3,4,5,6,7,8,9,0},"")))
ARGUMENTS
range: Range of cells that contain the characters that you want to count.
range: Range of cells that contain the characters that you want to count.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Count numeric characters in a cell | How to count the total number of numeric characters in a cell using Excel or VBA | |
| Count numeric characters in a cell | How to count the total number of numeric characters in a cell using Excel or VBA | |
| Count number of cells in a range | How to count the total number of cells in a range using Excel or VBA | |
| Count number of specific characters in a range | How to total number of specific characters in a range using Excel or VBA | |
| Count numeric cells in a range | How to count cells that contain numeric values in a specific range using Excel or 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 | |
| SUMPRODUCT Function | The Excel SUMPRODUCT function multiplies corresponding ranges and returns the sum of these values | |
| SUBSTITUTE Function | The Excel SUBSTITUTE function replaces characters with another in a specified string |