# How to Count the complete Numbers in a single cell

#### azad092

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

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

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

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

#### Peter_SSs

##### MrExcel MVP, Moderator
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.

Cell Formulas
RangeFormula
B2:B6B2=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)
A6A6=TRIM(REPT(" 10 aslam",100))

#### Amit Tandon

##### Board Regular

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!

Replies
4
Views
67
Replies
1
Views
64
Replies
32
Views
1K
Replies
4
Views
181
Replies
4
Views
141

Threads
1,113,872
Messages
5,544,796
Members
410,635
Latest member
phoenix7771