How to Count the complete Numbers in a single cell

azad092

Board Regular
Joined
Dec 31, 2019
Messages
149
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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,893
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,114,314
Messages
5,547,167
Members
410,775
Latest member
alal1030
Top