# 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

#### jasonb75

##### Well-known Member
It would be better to fix your method of entry rather than trying to find something that works with badly formatted data.

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

#### sandy666

##### Well-known Member
to use the above code what I have to do?
If I have to upgrade the MS Office or something else....? please guide me
you'll need 2010/2013 with PQ add-in or 2016 and above
in short:
Alt A PN O Q then Alt H Q and replace code there with copied from the post here
Power Query:
``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Count = Table.AddColumn(Source, "Count", each try List.Count(Text.Split(Text.Select([Column1],{"0".."9",","}),","))-1 otherwise 0)
in
Count``````
remeber that the name of the source table should be the same as in the code (or vice versa) - here it is Table1

#### Lee J

##### New Member
This will work for you but like jasonb75 said the method of entry needs fixing:

I used 3 as the number that represents "Shan" it might not be correct the following will need repeating for all name/number options you use and will only work if spelled correctly:

+IF(COUNTIF(A1,"*10*")=1,1,COUNTIF(A1,"*Aslam*"))

#### Rick Rothstein

##### MrExcel MVP
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
I think this modification to my UDF does what you are asking for here...
VBA Code:
``````Function NumCount(ByVal S As String) As Long
Dim X As Long, HasText As Boolean
HasText = Len(S) > 0
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)))
If NumCount = 0 And HasText Then NumCount = 1
End Function``````

##### Board Regular

I think this modification to my UDF does what you are asking for here...
VBA Code:
``````Function NumCount(ByVal S As String) As Long
Dim X As Long, HasText As Boolean
HasText = Len(S) > 0
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)))
If NumCount = 0 And HasText Then NumCount = 1
End Function``````
hi
good morning
you absolutely right understand......your message is a good news for me
thanks a lot dear

#### Peter_SSs

##### MrExcel MVP, Moderator
If you were interested in a non-looping approach, I think this UDF may also do what you want.

VBA Code:
``````Function NCount(s As String) As Long
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "\d+"
NCount = -(Len(s) > 0)
If .Test(s) Then NCount = .Execute(s).Count
End With
End Function``````

AB
1DataCount
21 ali, 2 asad , shan, 10 aslam3
30
4shan1
Count
Cell Formulas
RangeFormula
B2:B4B2=NCount(A2)

#### Lee J

##### New Member

[U]azad092[/U] should you not be receiving 4 as the result for "1 ali, 2 asad , shan, 10 aslam"?

#### Peter_SSs

##### MrExcel MVP, Moderator
[U]azad092[/U] should you not be receiving 4 as the result for "1 ali, 2 asad , shan, 10 aslam"?
Doesn't sound like it from post 10

.. or post 1
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
... the result should 3

#### Amit Tandon

##### Board Regular
In case each number is separated by a comma OR a space (the illustration shows each number is separated by a space), then to return the count of numbers in a cell refer below:

26Aug19.xlsx
AB
21 ali, 2 asad , shan, 10 aslam3
30
4shan1
51 ali, 2 asad , shan, 10, aslam3
Sheet41
Cell Formulas
RangeFormula
B2:B5B2=IF(A2="",0,MAX(SUM(--ISNUMBER(--TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2,","," ")," ",REPT(" ",LEN(A2))),(ROW(\$1:\$255)-1)*LEN(A2)+1,LEN(A2))))),1))
Press CTRL+SHIFT+ENTER to enter array formulas.

#### Peter_SSs

##### MrExcel MVP, Moderator
Here is another formula approach (2 forms), assuming there is a space after each number as in the samples given.
These will continue to work even if rows are subsequently inserted at the top of the sheet and will handle much longer strings, should they be possible.
Column B is obviously a slightly shorter formula but does use the volatile function INDIRECT. If you have a lot of these formulas in your sheet they could impact your sheet performance. The column C formula is a non-volatile version of the same approach if that is preferred.

Formulas may require Ctrl+Shift+Enter confirmation in your excel version.

ABC
1DataCount
21 ali, 2 asad , shan, 10 aslam33
300
4shan11
Count
Cell Formulas
RangeFormula
B2:B4B2=MAX(COUNT(MID(A2,ROW(INDIRECT("1:"&LEN(A2)-1)),1)/(MID(A2,ROW(INDIRECT("2:"&LEN(A2))),1)=" ")),LEN(A2)>0)
C2:C4C2=MAX(COUNT(MID(A2,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2)-1)),1)/(MID(A2,ROW(INDEX(A:A,2):INDEX(A:A,LEN(A2))),1)=" ")),LEN(A2)>0)

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