Count numeric characters in a cell
This tutorial shows how to count the total number of numeric characters in a cell through the use of an Excel formula or VBA
Example: Count numeric characters in a cell
|
=SUMPRODUCT(LEN(B5)-LEN(SUBSTITUTE(B5,{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 specific cell. In this example the formula will only count numbers 1234 from cell B5, which is four characters in total.
|
Sub Count_numeric_characters_in_a_cell()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'count the total number of numeric characters in a cell
ws.Range("C5").Formula = "=SUMPRODUCT(LEN(B5)-LEN(SUBSTITUTE(B5,{1,2,3,4,5,6,7,8,9,0},"""")))"
ws.Range("C5").Formula = "=SUMPRODUCT(LEN(B5)-LEN(SUBSTITUTE(B5,{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.
Cell to count from: Select the cell that captures the characters that you want to count by changing the cell reference ("B5") in the VBA code.
Worksheet Selection: Select the worksheet which captures the cell 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.
Cell to count from: Select the cell that captures the characters that you want to count by changing the cell reference ("B5") in the VBA code.
Worksheet Selection: Select the worksheet which captures the cell 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 the total number of numeric characters (0-9) in a cell through the use of an Excel formula or VBA.
This tutorial provides one Excel method which uses the SUMPRODUCT, SUBSTITUTE and LEN functions, to count the number of numeric characters in a cell.
The VBA method uses the VBA formula function and the exact same formula that is provided in the Excel method.
FORMULA
=SUMPRODUCT(LEN(string)-LEN(SUBSTITUTE(string,{1,2,3,4,5,6,7,8,9,0},"")))
=SUMPRODUCT(LEN(string)-LEN(SUBSTITUTE(string,{1,2,3,4,5,6,7,8,9,0},"")))
ARGUMENTS
string: The string from which to extract the numeric characters.
string: The string from which to extract the numeric characters.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Count numeric characters in a range | How to count the total number of numeric characters in a range using Excel and VBA | |
| Count number of specific characters in a cell | How to count the total number of specific characters in a cell through 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 methods | |
| 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 | |
| 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 |