How to Count the complete Numbers in a single cell

[azad092]

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]

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

 

[sandy666]

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

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]

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

=IF(COUNTIF(A1,"*1*")=1,1,COUNTIF(A1,"*Ali*"))+IF(COUNTIF(A1,"*2*")=1,1,COUNTIF(A1,"*Asad*"))+IF(COUNTIF(A1,"*3*")=1,1,COUNTIF(A1,"*Shan*"))+IF(COUNTIF(A1,"*10*")=1,1,COUNTIF(A1,"*Aslam*"))

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]

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

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

 

[azad092]

I think this modification to my UDF does what you are asking for here...

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]

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

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

azad092 2020-08-21 1.xlsm
	A	B
1	Data	Count
2	1 ali, 2 asad , shan, 10 aslam	3
3		0
4	shan	1
Count

Cell Formulas
Range	Formula
B2:B4	B2	=NCount(A2)

 

[Lee J]

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

 

[Peter_SSs]

[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]

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
	A	B
2	1 ali, 2 asad , shan, 10 aslam	3
3		0
4	shan	1
5	1 ali, 2 asad , shan, 10, aslam	3
Sheet41

Cell Formulas
Range	Formula
B2:B5	B2	=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]

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.

azad092 2020-08-21 1.xlsm
	A	B	C
1	Data	Count
2	1 ali, 2 asad , shan, 10 aslam	3	3
3		0	0
4	shan	1	1
Count

Cell Formulas
Range	Formula
B2:B4	B2	=MAX(COUNT(MID(A2,ROW(INDIRECT("1:"&LEN(A2)-1)),1)/(MID(A2,ROW(INDIRECT("2:"&LEN(A2))),1)=" ")),LEN(A2)>0)
C2:C4	C2	=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)