Working with Excel is really very interesting. Lots of things can be done with the help of Excel. When working with Excel we sometimes face that unnecessary text characters are added to our data. In this article, we will discuss how to count numbers in a cell in Excel.

## Download Practice Workbook

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

## 3 Methods to Count Numbers in a Cell in Excel

We will apply the following methods to count the numbers in a cell.

In the sample dataset, we are showing some customers and their bills.

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

**The LEN function** returns the number of characters in a text string.

**Syntax:**

**LEN(text)**

**Argument:**

** Text **â€“ It is the reference whose length we want to find. Spaces count as characters.

Here, we will simply use the **LEN **function to count numbers in a cell.

**Step 1:**

- Add a column to show the count of numbers.

**Step 2:**

- Go to
**Cell D5**. - Write the following formula:

`=LEN(C5)`

**Step 3:**

- Then, press
**Enter**.

**Step 4:**

- Now, pull the
**Fill Handle**icon to the last.

We see that the number count is showing on the right. This function counts everything it finds in a cell. If the cells contain only numbers, we can simply use this function.

**Read more:** **Count the Number of Specific Characters in a Cell in Excel (2 Approaches)**

### 2. Count Only Numbers with SUM+LEN+SUBSTITUTE

**The SUM function** adds values. We can add individual values, cell references or ranges, or a mixture of all three.

**Syntax:**

**SUM(number1,[number2],â€¦)**

**Argument:**

** number1 **â€“ It is the first number we want to add. We can add up to 255 numbers in this way.

**The SUBSTITUTE function** is used to replace specific text in a text string.

**Syntax:**

**SUBSTITUTE(text, old_text, new_text, [instance_num])**

**Arguments**:

** Text **â€“ It is the text or the reference to a cell containing text. And from that text, we will substitute characters.

** Old_text **â€“ It is the text we want to replace.

** New_text** â€“ It is the text we want to replace old_text with.

** Instance_num** â€“ This is optional. It specifies which occurrence of old_text we want to replace with new_text.

Here, we will use multiple functions to count numbers. If any text is mixed with numbers, we can avoid that text by this method. We modify the data and add some text on **column C** to apply this method.

**Step 1:**

- Go to
**Cell D5**. - Write down the formula. The formula is:

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

**Step 2:**

- Then, press
**Enter**.

**Step 3:**

- Drag the
**Fill Handle**icon to the last.

Here, we see that the formula is counting only numbers and avoiding the texts. It also avoids the special characters.

**Formula Breakdown:**

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

It substitute **0-9** from **cell C5**.

**Output:** {a864,590; a864,590; a864,590; a86,590; a864,90; a84,590; a864,590; a64,590; a864,50; a864,59}

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

It gives the length of each object from the substitute value.

**Output: {8, 8, 8, 7, 7, 7, 8, 7, 7, 7}**

**LEN(C5)**

Get the total length of **cell C5**.

**Output: 8**

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

Substruction is applied here.

**Output: { â€“ Â Â Â Â 0Â Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â 1Â Â Â Â Â Â Â Â Â 1Â Â Â Â Â Â Â Â Â 1Â Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â 1Â Â Â Â Â Â Â Â Â 1Â Â Â Â Â Â Â Â Â 1}**

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

This will sum the subtructed result.

**Output: 6**

**Read more: How to Count Number of Characters in a Cell in Excel (Easiest 6 Ways)**

### 3. Count Numbers Using VBA Codes in Excel

Here, we will apply the **VBA **code to count the total number in a cell.

**Step 1:**

- First, modify the data as per our desire.

**Step 2:**

- Then, press
**Alt+F11**to go enter the**VBA** - Now, press the
**Insert**from the menu of**VBA**and select the**Module**.

**Step 3:**

- Now, write the below code on the command module.
- Then, save and close the window.

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

**Step 4:**

- Now, go to
**Cell D5**. - Write
**Letters_Numbers**This function is added by the**VBA**code. - Complete the formula. So, the formula becomes:

`=Letters_Numbers(C5)`

**Step 5:**

- Now, press
**Enter**.

**Step 6:**

- Pull the
**Fill Handle**icon to the last cell.

Here, we added an **N **with the numeric values. We can also present Text values by changing the **VBA** code.

**Step 7:**

- We changed the code and closed it.

**Step 8:**

- Again, apply the formula through the range
**D5**to

For, text values we added **L** with each count.

**Read More:** **Excel VBA: Count Characters in Cell (5 Methods)**

## How to Count All Kind of Characters?

**Step 1:**

- We made some changes to the data set. Now, it looks:

**Step 2:**

- We apply a combination of
**LEN**and**SUBSTITUTE**functions here. So, the formula will be:

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

**Step 3:**

- Press the
**Enter**button.

**Step 4:**

- Pull the
**Fill Handel**icon to the**Cell D9**.

Here, we get results only for the text values of our desired cells.

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

**Output: 1**

## Conclusion

We explained 3 methods to count numbers in a cell in Excel. Hope this will help you to get the desired solution. Please have a look at our website **Exceldemy.com** and give your suggestions in the comment box.

## Related Articles

**Count Specific Characters in a Column in Excel: 4 Methods****How to Count Characters in Cell without Spaces in Excel (4 Methods)****How to Count Characters in Cell up to Space in Excel****Excel Count Specific Characters in Cell (4 Quick Ways)****How to Count Characters in Cell Including Spaces in Excel (5 Methods)**