In this article, we will demonstrate** 5 **easy ways to determine how many digits a number in a cell has.

## Why Count Numbers in a Cell in Excel?

Counting numbers in a cell in Excel can be useful for a variety of reasons:

**Data Analysis:**You can figure out how often certain values or categories appear within a group of cells by looking at their frequency or occurrence.**Data Validation:**For instance, you might want to make sure that a certain amount of entries or values are present in a group of cells. By counting the numbers, you can confirm if the right number of entries is there.**Reporting and Summarizing:**You can tally the number of times certain values or criteria appear to make charts, graphs, or tables.**Conditional Logic:**You can use functions like**COUNTIF**or**COUNTIFS**to tally numbers based on specific conditions. This helps to sort and study data based on different situations.**Data Cleanup:**You can count how many cells are empty or contain errors to find problems with the data’s quality or missing values.**Data Entry and Verification:**You can keep track of the number of things you’ve recorded or check if you’ve entered all the necessary information.

## How to Count Numbers in a Cell in Excel

In the following dataset, we have **Employee Name** and **Yearly Salary** columns. We will count the numbers in the cells of the **Yearly Salary** column.

We used Excel 365 here, but you can use any available Excel version.

### Method 1 Using the LEN Function in a Cell

The **LEN **function counts the number of characters in a cell value. It counts every character it finds in a cell. If the cells contain only numbers, this is the simplest method for counting numbers in them.

**STEPS:**

- Enter the following formula in cell
**D5**:

`=LEN(C5)`

- Press
**ENTER**.

The result is shown in cell **D5**.

- Drag down the formula with the
**Fill Handle**to fill the cells below.

We now have the complete **Numbers in Cells** column.

### Method 2 – Count Only Numbers in a Cell with SUM, LEN, and SUBSTITUTE Functions

To count only the numbers in a cell, we can use the combination of **SUM**, **LEN**, and **SUBSTITUTE** functions.

**STEPS:**

- Enter the following formula in cell
**D5**:

`=SUM(LEN(C5)-LEN(SUBSTITUTE(C5,{1,2,3,4,5,6,7,8,9,0},)))`

- Press
**ENTER**.

The result is displayed in cell **D5**.

- Drag down the formula with the
**Fill Handle**.

The complete **Numbers in Cells** column is displayed.

**Formula Breakdown**

**SUM(LEN(C5)-LEN(SUBSTITUTE(C5,{1,2,3,4,5,6,7,8,9,0},)))**

**LEN(C5)**→ calculates the length of the text in cell**C5**.**SUBSTITUTE(C5,{1,2,3,4,5,6,7,8,9,0},)**→ replaces all occurrences of the numbers**1, 2, 3, 4, 5, 6, 7, 8, 9,**and**0**with an empty string in the text from cell**C5**.**LEN(SUBSTITUTE(C5,{1,2,3,4,5,6,7,8,9,0},))**→ calculates the length of the text after the substitution. It counts the number of characters in the text string after removing the numbers.**SUM(LEN(C5)-LEN(SUBSTITUTE(C5,{1,2,3,4,5,6,7,8,9,0},)))**→ calculates the sum of the differences between the length of the original text (before substitution) and the length of the text after substitution. This gives you the total count of numeric characters (0-9) in the original text.**SUM(LEN(C5)-LEN(SUBSTITUTE(C5,{1,2,3,4,5,6,7,8,9,0},)))**→ becomes**Output: 3**

### Method 3 – Using VBA Codes to Count Numbers in a Cell in Excel

**STEPS:**

- To open the
**VBA Editor**window, go to the**Developer**tab and select**Visual Basic**. - Alternatively, use the keyboard shortcut
**ALT+F11**to bring up the**VBA Editor**window.

A **VBA Editor** window will appear.

- From the
**Insert**tab, select**Module**.

- Enter the following code in the
**Module**:

```
Function count_numbers_in_cell(pInput As String) As String
Dim x_range As Object
Dim x_mc As Object
Dim x_m As Object
Dim x_output As String
Set x_range = CreateObject("vbscript.regexp")
x_range.Global = True
x_range.Ignorecase = True
x_range.Pattern = "[^\w]"
count_numbers_in_cell = ""
If Not x_range.test(pInput) Then
x_range.Pattern = "(\d+)"
Set x_mc = x_range.Execute(pInput)
For Each x_m In x_mc
x_output = x_output & (x_m.Length & IIf(IsNumeric(x_m), "N", "L"))
Next
count_numbers_in_cell = x_output
End If
End Function
```

**Code Breakdown**

**Function count_numbers_in_cell(input As String) As String**

- defines a VBA function named
**count_numbers_in_cell**that takes a string parameter input and returns a string as the result.

**Dim x_range As Object**

**Dim x_mc As Object**

**Dim x_m As Object**

- declare the variables
**x_range**,**x_mc**, and**x_m**as**Object**data types.

**Dim x_output As String**

- declares a variable
**x_output**as a**String**data type.

**Set x_range = CreateObject(“vbscript.regexp”)**

- creates a regular expression object using the
**CreateObject**function and assigns it to the variable**x_range**. The regular expression object is specific to**VBScript**.

**x_range.Global = True**

- sets the
**Global**property of the regular expression object to**True**, which enables global matching (matching all occurrences) rather than just the first match.

**x_range.Ignorecase = True**

- sets the
**Ignorecase**property of the regular expression object to**True**, which makes the matching case insensitive.

**x_range.Pattern = “[^\w]”**

- sets the
**Pattern**property of the regular expression object to the pattern**“[^\w]”**. This pattern matches any character that is not a word character (alphanumeric or underscore).

**count_numbers_in_cell = “”**

- initializes the return value of the function to an empty string.

**If Not x_range.test(pInput) Then**

- uses the
**Then**statement to check if the regular expression pattern does not match the input string.

**x_range.Pattern = “(\d+)”**

- sets the
**Pattern**property of the regular expression object to the pattern**“(\d+)”**. This pattern matches one or more digits.

**Set x_mc = x_range.Execute(pInput)**

- executes the regular expression pattern of the input string and stores the matches in the variable
**xMc**.

**For Each x_m In x_mc**

- starts a
**Next**loop that iterates over each match stored in the variable**xMc**, assigning each match to the variable**xM**.

**x_output = x_output & (x_m.Length & IIf(IsNumeric(x_m), “N”, “L”))**

- appends the length of the current match (
**Length**) and either**“N”**or**“L”**to the**xOut**string. If the match is numeric (determined by IsNumeric(xM)),**“N”**is appended; otherwise,**“L”**is appended.

**Next**

- indicates the end of the loop.

**count_numbers_in_cell = xOut**

- assigns the value of the
**xOut**string to the return value of the function**count_numbers_in_cell**.

**Save**the code and go back to our worksheet.- Enter the following formula in cell
**D5**:

`=count_numbers_in_cell(C5)`

- Press
**ENTER**.

The result is displayed in cell **D5**.

- Drag down the formula with the
**Fill Handle tool**.

The complete** Numbers in Cells** column is displayed.

### Method 4 – Using the LEN, FORMULATEXT, and SUBSTITUTE Functions

To see how many numbers have been summed together in a cell, we can use the** LEN**, **FORMULATEXT**, and **SUBSTITUTE** functions.

In the dataset below, we have Employees and their Salaries over several months.

**STEPS:**

`=C5+D5+E5+F5`

- Press
**ENTER**.

The result is displayed in cell **G5**.

- Drag down the formula with the
**Fill Handle tool**.

The complete** Total **column is displayed.

Now we can find the text form of the formula which we have entered in the **Total** column.

- Enter the following formula in cell
**H5**:

`=FORMULATEXT(G5)`

- Press
**ENTER**.

The result appears in cell **H5**.

- Drag down the formula with the
**Fill Handle tool**.

The complete** Formula Text **column is filled.

The formula **=C5+D5+E5+F5 **contains **4** numbers in it.

Now we can use a formula containing the **LEN**, **FORMULATEXT,** and **SUBSTITUTE** functions to count the numbers that were summed up in the cells of the **Total** column.

- Enter the following formula in cell
**I5**:

`=LEN(FORMULATEXT(G5))-LEN(SUBSTITUTE(FORMULATEXT(G5),"+",""))+1`

- Press
**ENTER**.

The result in cell **I5 **is **4**, proving that the formula in cell** G5** adds** 4** numbers.

- Drag down the formula with the
**Fill Handle tool**.

The complete** Numbers **column is filled.

**Formula Breakdown**

**LEN(FORMULATEXT(G5))-LEN(SUBSTITUTE(FORMULATEXT(G5),”+”,””))+1**

**FORMULATEXT(G5)**→ returns the formula as text from cell**G5**.**SUBSTITUTE(FORMULATEXT(G5),”+”,””)**→ replaces all occurrences of the “+” symbol in the formula with an empty string.**LEN(SUBSTITUTE(FORMULATEXT(G5),”+”,””))**→ calculates the length of the formula after removing the “+” symbols.**LEN(FORMULATEXT(G5))**→ calculates the length of the original formula in cell**G5**.**LEN(FORMULATEXT(G5)) – LEN(SUBSTITUTE(FORMULATEXT(G5),”+”,””))**→ calculates the difference between the lengths of the original formula and the formula with the “+” symbols removed.- .
**LEN(FORMULATEXT(G5))-LEN(SUBSTITUTE(FORMULATEXT(G5),”+”,””))+1**→ becomes**Output: 4**

### Method 5. Using VBA Code to Count Numbers in a Formula

We can use VBA code to count numbers that were summed up in a formula.

**STEPS:**

- Complete the
**Total**column by manually entering the formula. **Following the steps of Method 4**, fill the**Formula Text**column.**Following the steps of Method 3**, open the**VBA Editor**window and insert a**Module**.- Enter the following code in the
**Module**:

```
Function count_numbers_in_formula(Rng As Range)
x_cell = Split(Rng.Formula, "+")
count_numbers_in_formula = UBound(x_cell) + 1
End Function
```

**Code Breakdown**

**Function count_numbers_in_formula(Rng As Range)**

- defines a function named
**count_numbers_in_formula**that takes a single argument**Rng**of type**Range**.

**x = Split(Rng.Formula, “+”)**

- uses
**the Split function**to split the formula of the**Rng**range based on the “+”. It assigns the resulting array to the variable**x**. Each element of the array will contain a portion of the formula that was separated by the “+” symbol.

**count_numbers_in_formula = UBound(x) + 1**

- assigns the count of elements in the
**x**array to the**count_numbers_in_formula**.**The UBound function**is used to determine the upper bound of the array**x**, and adding**1**gives the count of elements. This count represents the number of numbers present in the formula.

**End Function**

- marks the end of the
**count_numbers_in_formula**function.

**Save**the code and go back to our worksheet.- Enter the following formula in cell
**I5**:

`=count_numbers_in_formula(G5)`

- Press
**ENTER**.

The result appears in cell **I5**.

- Drag down the formula with the
**Fill Handle**.

The complete** Numbers** column is filled.

**Read More: **How to Find 5 Most Frequent Numbers in Excel

## How to Count All Characters in a Column in Excel

We can count all characters (Numeric Values, Non-Numeric Values, and Special Characters) in a column in Excel by using the **LEN **and **SUBSTITUTE **functions.

Here, we will count all characters in the cells of the **Pass Code** column.

**STEPS:**

- Enter the following formula in cell
**D5**:

`=LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C5,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""))`

- Press
**ENTER**.

The result appears in cell **D5**.

- Drag down the formula with the
**Fill Handle**.

The complete **Characters in a Cell** column is filled.

**Formula Breakdown**

**SUBSTITUTE(C5,0,””)**

- substitutes
**0**from cell**C5**with a null value.**Output: 86459s**

- By nesting this function, we substitute all numeric values from Cell
**C5**.

**SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C5,0,””),1,””),2,””),3,””),4,””),5,””),6,””),7,””),8,””),9,””)**

- All numeric values will be removed by applying this formula.
**Output: s**

**LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C5,0,””),1,””),2,””),3,””),4,””),5,””),6,””),7,””),8,””),9,””))**

- counts the length after substituting all the numeric values.

**LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C5,0,””),1,””),2,””),3,””),4,””),5,””),6,””),7,””),8,””),9,””))** → becomes

**Output: 1**

## How to Count Non-Numeric Numbers in a Cell in Excel

We can count the non-numeric numbers in a cell in Excel via two different methods: using a formula, and using VBA code.

**Case 1 – Using a Formula**

We can use the** LEN** and **SUBSTITUTE** functions to count non-numeric numbers in a cell in Excel.

**STEPS:**

- Enter the following formula in cell
**D5**:

`=LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C5,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""))`

- Press
**ENTER**.

The result appears in cell **D5**.

- Drag down the formula with the
**Fill Handle tool**.

The complete **Non-Numeric** **Characters in a Cell** column is filled.

**Case 2 – Using VBA**

**STEPS:**

**Following the steps of Method 3**, open the**VBA Editor**window and insert a**Module**.- Enter the following code in the
**Module**:

```
Function count_Non_Numeric_Characters(pInput As String) As String
Dim x_range As Object
Dim x_mc As Object
Dim x_m As Object
Dim x_output As String
Set x_range = CreateObject("vbscript.regexp")
x_range.Global = True
x_range.Ignorecase = True
x_range.Pattern = "[^\w]"
count_Non_Numeric_Characters = ""
If Not x_range.test(pInput) Then
x_range.Pattern = "([a-z]+)"
Set x_mc = x_range.Execute(pInput)
For Each x_m In x_mc
x_output = x_output & (x_m.Length & IIf(IsNumeric(x_m), "N", "L"))
Next
count_Non_Numeric_Characters = x_output
End If
End Function
```

**Save**the code and go back to our worksheet.- Enter the following formula in cell
**D5**:

`=count_Non_Numeric_Characters(C5)`

- Press
**ENTER**.

The result appears in cell **D5**.

- Drag down the formula with the
**Fill Handle tool**.

The complete** Non-Numeric Characters in a Cell **column is filled.

## How to Count Number of Cells That Have Numbers Using the COUNT Function

In the dataset below, some cells contain numbers and others contain non-numeric values.

Using** the COUNT function**, we can count the number of cells that have numbers in them.

**STEPS:**

- Enter the following formula in cell
**C16**.

`=COUNT(B5:C14)`

- Press
**ENTER**.

The result is returned in cell **C16**.

## How to Count Cells in a Data Range: 4 Ways

We can also count non-empty cells, only empty cells, cells based on a single criterion, and cells based on multiple criteria in a range in Excel.

We will use **COUNTA**, **COUNTBLANK**, **COUNTIF**, and **COUNTIFS** functions to do the task.

** Case 1 – ****Counting Non-Empty Cells**

**STEPS:**

- Enter the following formula in cell
**E16**:

`=COUNTA(B5:E14)`

The** COUNTA** function calculates the number of non-empty cells in a data range.

- Then, press
**ENTER**.

The number of non-empty cells is returned in cell **E16**.

**Case 2 – Counting Empty Cells in a Dataset**

We can use the **COUNTBLANK **function to count all the empty cells in a dataset.

**STEPS:**

- Enter the following formula in cell
**E17**:

`=COUNTBLANK(B5:E14)`

- press
**ENTER**.

The number of empty cells is returned in cell **E17**.

**Case 3 – Counting Cells Based on Single Criterion**

We can use the **COUNTIF **function to count cells based on a single criterion. Here, we set the criterion as** Engineer**. Thus, we will count the cells that have an **Engineer** in them.

**STEPS:**

- Enter the following formula in cell
**E18**:

`=COUNTIF(C5:C14,"Engineer")`

- Press
**ENTER**.

The number of cells containing the criterion **Engineer** is returned in cell **E18**.

**Case 4 – Count Cells Based on Multiple Criteria**

We can use the **COUNTIFS **function to count cells based on multiple criteria. Here, we will count the cells that have an **Engineer** in them, and the cells that contain a value equal to **$10,000**.

**STEPS:**

- Enter the following formula in cell
**E19**:

`=COUNTIFS(C5:C14,"Engineer",E5:E14,"="& "10,000")`

- Press
**ENTER**.

The result is displayed in cell** E19**.

## How to Count Total Characters in a Data Range in Excel

We can use the **SUMPRODUCT** and** LEN** functions for this task.

**STEPS:**

- Enter the following formula in cell
**C16**:

`=SUMPRODUCT(LEN(B5:C14))`

- Press
**ENTER**.

The total number of characters in the range is returned in cell **C16**.

## Character Restrictions for Excel Cells

Microsoft Excel has limitations on how much text you can put in a cell:

- The most characters a cell can hold is 32,767.
- In a cell itself, you can only see up to 1,024 characters. However, the Formula bar can show all 32,767 characters.
- The longest formula you can have in Excel 2007 and later versions is 8,192 characters (1,014 in Excel 2003).

**Download Practice Workbook**

## Related Articles

**<< Go Back to Excel COUNT Function ****|**** Excel Functions ****|**** Learn Excel**