How to Count the complete Numbers in a single cell

azad092

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

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,052
Office Version
  1. 2019
Platform
  1. Windows
It would be better to fix your method of entry rather than trying to find something that works with badly formatted data.
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,128
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
Power Query:
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

New Member
Joined
Jun 30, 2020
Messages
43
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,435
Office Version
  1. 2010
Platform
  1. Windows
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...
VBA Code:
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

Board Regular
Joined
Dec 31, 2019
Messages
152
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

I think this modification to my UDF does what you are asking for here...
VBA Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,915
Office Version
  1. 365
Platform
  1. Windows
If you were interested in a non-looping approach, I think this UDF may also do what you want.

VBA Code:
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
AB
1DataCount
21 ali, 2 asad , shan, 10 aslam3
30
4shan1
Count
Cell Formulas
RangeFormula
B2:B4B2=NCount(A2)
 

Lee J

New Member
Joined
Jun 30, 2020
Messages
43
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,915
Office Version
  1. 365
Platform
  1. Windows
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
ABC
1DataCount
21 ali, 2 asad , shan, 10 aslam33
300
4shan11
Count
Cell Formulas
RangeFormula
B2:B4B2=MAX(COUNT(MID(A2,ROW(INDIRECT("1:"&LEN(A2)-1)),1)/(MID(A2,ROW(INDIRECT("2:"&LEN(A2))),1)=" ")),LEN(A2)>0)
C2:C4C2=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)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,604
Messages
5,548,934
Members
410,885
Latest member
melifreeman
Top