How to Count the complete Numbers in a single cell

azad092

Board Regular
Joined
Dec 31, 2019
Messages
148
Office Version
  1. 2007
Platform
  1. Windows
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
Joined
May 23, 2020
Messages
84
Office Version
  1. 365
Platform
  1. Windows
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".
 

Some videos you may like

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
Joined
May 28, 2005
Messages
46,825
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 23, 2020
Messages
84
Office Version
  1. 365
Platform
  1. Windows

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!
 

Watch MrExcel Video

Forum statistics

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