# How to Count the complete Numbers in a single cell

##### Board Regular
Hi Dear members
I want to count the complete numeric values from a single cell, I am using below formula but it not work perfectly as I want
for example in Cell A1 I enter data as under
1 ali, 2 asad , shan, 10 aslam
in the above data 1,2,10 is used as the serial numbers
=COUNT(LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9,0},)))
by using above formula for above data the result should 3 but the result is different
If anybody knows the correct formula please guide me

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### sandy666

##### Well-known Member
with Power Query
 Column1 Count 1 ali, 2 asad , shan, 10 aslam 3

Power Query:
``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Count = Table.AddColumn(Source, "Count", each List.Count(Text.Split(Text.Select([Column1],{"0".."9",","}),","))-1)
in
Count``````

#### sandy666

##### Well-known Member
oops, I noticed you are on XL2007 so ignore post above

#### jasonb75

##### Well-known Member
Multiple items in a single cell is quite possibly the second worst thing you can do in excel.

Any formula for something like that will only be as good as the consistency with which the data is entered, this works with the example provided but as with your original formula, it might not work with everything.

=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1

#### Rick Rothstein

##### MrExcel MVP

Here is a UDF (user defined function) that you can use...
VBA Code:
``````Function NumCount(ByVal S As String) As Long
Dim X As Long
For X = 1 To Len(S)
If Not Mid(S, X, 1) Like "#" Then Mid(S, X) = " "
Next
NumCount = 1 + UBound(Split(Application.Trim(S)))
End Function``````
HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NumCount just like it was a built-in Excel function. For example,

=NumCount(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

#### Lee J

##### New Member

Not going to be neat but what about simply using this:

=COUNTIF(A1,"*1*")+COUNTIF(A1,"*2*")+COUNTIF(A1,"*3*")

Adding a new +COUNTIF(A1,"*?*") for every number that could be in A1

You could use:

=SUM(LEN(A1)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,"")))-1

But if you go above 10 its going to get messy as 11 will get replaced with the 1's

##### Board Regular

with Power Query
 Column1 Count 1 ali, 2 asad , shan, 10 aslam 3

Power Query:
``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Count = Table.AddColumn(Source, "Count", each List.Count(Text.Split(Text.Select([Column1],{"0".."9",","}),","))-1)
in
Count``````
to use the above code what I have to do?
If I have to upgrade the MS Office or something else....? please guide me

##### Board Regular
Here is a UDF (user defined function) that you can use...
VBA Code:
``````Function NumCount(ByVal S As String) As Long
Dim X As Long
For X = 1 To Len(S)
If Not Mid(S, X, 1) Like "#" Then Mid(S, X) = " "
Next
NumCount = 1 + UBound(Split(Application.Trim(S)))
End Function``````
HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NumCount just like it was a built-in Excel function. For example,

=NumCount(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
hi
I have used your coding and formula as you guide me... but it only return 1

#### Rick Rothstein

##### MrExcel MVP
I have used your coding and formula as you guide me... but it only return 1
What text was in the cell that you passed into the function?

##### Board Regular
What text was in the cell that you passed into the function?
Hi Rick
actually A1 was the column heading that's why it was not working properly.... but its a good work by you...
one thing more that I want that I want to a part of coding more.....for if there no numbers in the cell but have any text like a name etc than it should return the value 1
for examlple
in A2
we enter data as 1 ali, 2 asad , shan, 10 aslam ..... and it return 3
but if we enter only text without any number than it return 1
but also remember that in case of blank cell it should return 0

Replies
4
Views
72
Replies
1
Views
68
Replies
32
Views
1K
Replies
4
Views
185
Replies
4
Views
151