Count Numbers in a Cell in Excel (3 Methods)

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.

Data set for Count Numbers in a Cell in Excel


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)

Count Numbers with the LEN Function in a Cell

Step 3:

  • Then, press Enter.

Step 4:

  • Now, pull the Fill Handle icon to the last.

Count Numbers with the LEN Function in a Cell

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},)))

Count Only Numbers with SUM+LEN+SUBSTITUTE

Step 2:

  • Then, press Enter.

Step 3:

  • Drag the Fill Handle icon to the last.

Count Only Numbers with SUM+LEN+SUBSTITUTE

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.

Count Numbers Using VBA Codes in Excel

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

 

Count Numbers Using VBA Codes in Excel

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)

Count Numbers Using VBA Codes in Excel

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.

Count Numbers Using VBA Codes in Excel

Step 8:

  • Again, apply the formula through the range D5 to

Count Numbers Using VBA Codes in Excel

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,""))

Count only Characters

Step 3:

  • Press the Enter button.

Step 4:

  • Pull the Fill Handel icon to the Cell D9.

Count Only Characters

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

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo