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

 

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

This formula works well for both comma and space separators. Also works well where the number appears in the end, for ex. "1 ali, 2 asad , shan, aslam 10".

 

[Peter_SSs]

This formula works well for .. comma ... separators. Also works well where the number appears in the end,

Sure, but so far we have not seen or heard mention of any such examples.

If such variations are possible then perhaps we should allow for any separator, embedded numbers in text, beginning or end, and not fail for subsequently inserted rows or until the string is much longer.

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
5	1 ali,2as55ad , shan, #10|aslam52	5
6	10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam	100
Count (2)

Cell Formulas
Range	Formula
B2:B6	B2	=MAX(COUNT(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)/NOT(ISNUMBER(MID(A2&".",ROW(INDIRECT("2:"&LEN(A2)+1)),1)+0))),LEN(A2)>0)
A6	A6	=TRIM(REPT(" 10 aslam",100))

 

[Amit Tandon]

Sure, but so far we have not seen or heard mention of any such examples.

If such variations are possible then perhaps we should allow for any separator, embedded numbers in text, beginning or end, and not fail for subsequently inserted rows or until the string is much longer.

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
5	1 ali,2as55ad , shan, #10|aslam52	5
6	10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam 10 aslam	100
Count (2)

Cell Formulas
Range	Formula
B2:B6	B2	=MAX(COUNT(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)/NOT(ISNUMBER(MID(A2&".",ROW(INDIRECT("2:"&LEN(A2)+1)),1)+0))),LEN(A2)>0)
A6	A6	=TRIM(REPT(" 10 aslam",100))

I have specifically provided for comma or space separated individual numbers which also consider the last number. Any number combined with text or special characters has not been considered. The string "1 ali, 2 asad , shan, 9aslam 10" returns the count of 3 and considers the numbers 1, 2 and 10 but does not consider 9. Because the OP's illustration displays both comma and space separators, I have expanded presumption basis this, and specifically stated so. Thanks much!