In this article, we will demonstrate** 5 **easy ways to do the task effortlessly.

For various purposes, like data analysis, data validation, data cleanup, and for many more reasons you might need to count numbers in a cell.

In a large dataset, where you have a bulk amount of data, counting numbers in a cell might seem hard, however, this is not the case. Rather, using some simple formulas and techniques, you can do the task in a blink of an eye.

In the following image, you can see the overview of counting numbers in a cell. So, let’s go through the following article so that you can do the task as well.

**Table of Contents**Expand

## Why Do You Need to Count Numbers in a Cell in Excel?

Counting numbers in a cell in Excel can be useful for a variety of reasons. Here are a few common needs for counting numbers in Excel cells:

**Data Analysis:**Counting numbers helps you analyze data in a simple way. 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:**Counting numbers can be useful for checking data. 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:**Counting numbers helps you make useful reports and summaries. You can tally the number of times certain values or criteria appear to make charts, graphs, or tables, which gives you a better understanding of your data.**Conditional Logic:**Counting numbers helps you use special rules in Excel. You can use functions like**COUNTIF**or**COUNTIFS**to tally numbers based on specific conditions. This helps you sort and study data based on different situations.**Data Cleanup:**Counting numbers can be useful when tidying up data. 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:**Counting numbers is helpful for keeping track of the number of things you’ve recorded or checking if you’ve entered all the necessary information. This is especially useful when dealing with lots of data or when there are specific rules for what needs to be entered.

## How to Count Numbers in a Cell in Excel: 5 Handy Methods

In the following dataset, you can see that we have **Employee Name** and **Yearly Salary** columns. Now, we will count the numbers in the cells of the **Yearly Salary** column. We will go through **5** methods to do the task.

Here, we used Excel 365. You can use any available Excel version.

### 1. Count Numbers with the LEN Function in a Cell

In this method, we will use **the LEN function** to count numbers in a cell in Excel.

- First of all, we will type the following formula in cell
**D5**.

`=LEN(C5)`

The **LEN **function counts the number of characters a cell value has. This function counts everything it finds in a cell. If the cells contain only numbers, we can simply use this function.

- After that, press
**ENTER**.

Then, you can see the result in cell **D5**.

- Moreover, we will drag down the formula with the
**Fill Handle**.

Thus, you can see the complete **Numbers in Cells** column.

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

In this method, we will use the combination of **SUM**, **LEN**, and **SUBSTITUTE** functions to count numbers in a cell in Excel.

- In the beginning, we will type the following formula in cell
**D5**.

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

- Then, press
**ENTER**.

Then, you can see the result in cell **D5**.

- Moreover, we will drag down the formula with the
**Fill Handle**.

Thus, you can see the complete **Numbers in Cells** column.

**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**

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

Here, we will use VBA code to count numbers in a cell in Excel.

- First, to bring out the VBA Editor window, we will go to the
**Developer**tab >> select**Visual Basic**.

You can also use the keyboard shortcut **ALT+F11** to bring out the VBA Editor window.

At this point, a VBA Editor window will appear.

- After that, from the
**Insert**tab >> select**Module**.

Then, we will type 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**

- This line 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**

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

**Dim x_output As String**

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

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

- This line 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**

- This line 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**

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

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

- This line 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 = “”**

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

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

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

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

- This line 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)**

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

**For Each x_m In x_mc**

- This line 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”))**

- This line 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**

- This line indicates the end of the loop.

**count_numbers_in_cell = xOut**

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

- After that,
**Save**the code >> go back to our worksheet. - Now, type the following formula in cell
**D5**.

`=count_numbers_in_cell(C5)`

- Then, press
**ENTER**.

Thus, you can see the result in cell **D5**.

- Furthermore, we will drag down the formula with the
**Fill Handle tool**.

As a result, you can see the complete** Numbers in Cells** column.

### 4. Applying LEN, FORMULATEXT, and SUBSTITUTE Functions

If you want to see how many numbers have been summed up together in a cell, then this method will be helpful for you. Here, we will use** LEN**, **FORMULATEXT**, and **SUBSTITUTE** functions to do the task.

In the following dataset, you can see that we have Employees and their several month’s salary.

`=C5+D5+E5+F5`

- Then, press
**ENTER**.

Thus, you can see the result in cell **G5**.

- Furthermore, we will drag down the formula with the
**Fill Handle tool**.

As a result, you can see the complete** Total **column.

Now, we will find the text form of the formula which we have written in the **Total** column.

- To do so, we will type the following formula in cell
**H5**.

`=FORMULATEXT(G5)`

- Next, press
**ENTER**.

Therefore, you can see the result in cell **H5**.

- Furthermore, we will drag down the formula with the
**Fill Handle tool**.

You can see the complete** Formula Text **column.

Thus, you can see the formula **=C5+D5+E5+F5 **contains **4** numbers in it.

Now, we will 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.

- Type the following formula in cell
**I5**.

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

- Next, press
**ENTER**.

You can see the result in cell **I5**.

The result is **4**, thus, it is proven that the formula of cell** G5** adds** 4** numbers.

- Furthermore, we will drag down the formula with the
**Fill Handle tool**.

You can see the complete** Numbers **column.

**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**

### 5. Use of VBA Code to Count Numbers in a Formula

Here, we will use VBA code to count numbers that were summed up in a formula.

- In the first place, complete the
**Total**column by manually typing the formula. - After that,
**following the steps of Method 4****Formula Text**column. - Then,
**following the steps of Method 3**, we bring out the**VBA Editor**window and inserted a**Module**. - After that, we type 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)**

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

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

- This line 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**

- This line 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**

- This line marks the end of the
**count_numbers_in_formula**

- After that, we
**Save**the code >> and go back to our worksheet. - Now, type the following formula in cell
**I5**.

`=count_numbers_in_formula(G5)`

- Then, press
**ENTER**.

Thus, you can see the result in cell **I5**.

- Furthermore, we will drag down the formula with the
**Fill Handle**.

As a result, you can see the complete** Numbers** column.

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

## How to Count All Characters in a Cell in Excel?

You 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.

- First of all, we will type 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,""))`

- After that, press
**ENTER**.

Then, you can see the result in cell **D5**.

- Moreover, we will drag down the formula with the
**Fill Handle**.

Thus, you can see the complete **Characters in a Cell** column.

**Formula Breakdown**

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

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

In this way, we will 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,””))**

- This will count 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?

Here, we will count the non-numeric numbers in a cell in Excel. Here, we will do the task two ways. Using formula, and using VBA code.

**Using Formula**

Here, we will use** LEN** and **SUBSTITUTE** functions to count non-numeric numbers in a cell in Excel.

- First, we will type 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,""))`

- Then, press
**ENTER**.

You can see the result in cell **D5**.

- Moreover, we will drag down the formula with the
**Fill Handle tool**.

As a result, you can see the complete **Non-Numeric** **Characters in a Cell** column.

**Using VBA**

Here, we will use VBA code to count Non-Numeric Characters in an Excel cell.

- In the beginning,
**following the steps of Method 3**, we brought out the**VBA Editor**window and inserted a**Module**. - After that, we type 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
```

After that, we **Save** the code >> and go back to our worksheet.

- Now, type the following formula in cell
**D5**.

`=count_Non_Numeric_Characters(C5)`

- Then, press
**ENTER**.

Thus, you can see the result in cell **D5**.

- Furthermore, we will drag down the formula with the
**Fill Handle tool**.

As a result, you can see the complete** Non-Numeric Characters in a Cell **column.

## How to Count Number of Cells That Has Numbers Using COUNT Function in Excel?

In the following dataset, you can see that we have some cells that contain numbers and others that contain non-numeric values.

Now using** the COUNT function**, we will count the number of cells that have numbers in them.

- First, we type the following formula in cell
**C16**.

`=COUNT(B5:C14)`

The **COUNT** function calculates the amount of cells that contain numbers in them.

- Then, press
**ENTER**.

Thus, you can see the result in cell **C16**.

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

Here, we will show how you can count non-empty cells, only empty cells, cells based on a single criterion, and cells based on multiple criteria in Excel.

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

** ****Count Non-Empty Cells**

Here, we will use the **COUNTA **function to count all the non-empty cells of a dataset.

- In the first place, we will type 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**.

Thus, you can see the number of non-empty cells in cell **E16**.

**Count Empty Cells in a Dataset**

Here, we will use the **COUNTBLANK **function to count all the empty cells of a dataset.

- First, we will type the following formula in cell
**E17**.

`=COUNTBLANK(B5:E14)`

The** COUNTBLANK** function calculates the number of empty cells in a data range.

- Then, press
**ENTER**.

Therefore, you can see the number of empty cells in cell **E17**.

**Count Cells Based on Single Criterion**

Here, we will 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.

- In the beginning, we will type the following formula in cell
**E18**.

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

The** COUNTIF** function calculates the number of cells based on a criterion.

- Then, press
**ENTER**.

Therefore, you can see the number of cells containing the criterion **Engineer** in cell **E18**.

**Count Cells Based on Multiple Criteria**

Here, we will 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**. Thus, we have **2** criteria.

- In the beginning, we will type the following formula in cell
**E19**.

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

The** COUNTIFS** function calculates the number of cells based on multiple criteria.

- Then, press
**ENTER**.

Therefore, you can see the result in cell** E19**.

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

Here, we will use the **SUMPRODUCT** and** LEN** functions to count the total number of characters in a data range.

- First of all, we will type the following formula in cell
**C16**.

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

- Then, press
**ENTER**.

Therefore, you can see the total number of characters in cell **C16**.

## Character Restrictions for Excel Cells

Microsoft Excel has a limit on how much text you can put in a cell. If you’re working with Excel worksheets that contain a lot of text, it’s important to be aware of the limitations. Here, we are providing the limitations for your benefits.

- The most characters a cell can hold is 32,767.
- In a cell, 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 is 8,192 characters in Excel 2007 and newer versions (1,014 in Excel 2003).
- When you’re working with large amounts of text in Excel, it’s important to keep these limitations in mind, especially when merging or importing data from another source.

These are some helpful tips for counting characters in Excel.

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

## Conclusion

In this article, we described 5 easy ways to count numbers in a cell in Excel. We extensively dove into all the methods with easy examples.

In addition to this, we describe how you can count all characters in a cell in Excel. Also, we describe how to count non-numeric numbers in a cell, the numbers of cells that contain numbers, and 4 ways to calculate cells in a dataset. Furthermore, we describe how to count total characters in a dataset and character restrictions for Excel cells.

We hope this article is helpful for you. Thank you for reading this article. If you have any queries or suggestions, please let us know in the comment section.

## Related Articles

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