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

 

[sandy666]

with Power Query

Column1	Count
1 ali, 2 asad , shan, 10 aslam	3

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Count = Table.AddColumn(Source, "Count", each List.Count(Text.Split(Text.Select([Column1],{"0".."9",","}),","))-1)
in
    Count

 

[sandy666]

oops, I noticed you are on XL2007 so ignore post above

 

[jasonb75]

Multiple items in a single cell is quite possibly the second worst thing you can do in excel.

Any formula for something like that will only be as good as the consistency with which the data is entered, this works with the example provided but as with your original formula, it might not work with everything.

=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1

 

[Rick Rothstein]

Here is a UDF (user defined function) that you can use...

Function NumCount(ByVal S As String) As Long
  Dim X As Long
  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)))
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NumCount just like it was a built-in Excel function. For example,

=NumCount(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Lee J]

Hi azad092,

Not going to be neat but what about simply using this:

=COUNTIF(A1,"*1*")+COUNTIF(A1,"*2*")+COUNTIF(A1,"*3*")

Adding a new +COUNTIF(A1,"*?*") for every number that could be in A1

You could use:

=SUM(LEN(A1)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,"")))-1

But if you go above 10 its going to get messy as 11 will get replaced with the 1's

 

[azad092]

with Power Query

Column1	Count
1 ali, 2 asad , shan, 10 aslam	3

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Count = Table.AddColumn(Source, "Count", each List.Count(Text.Split(Text.Select([Column1],{"0".."9",","}),","))-1)
in
    Count

to use the above code what I have to do?
If I have to upgrade the MS Office or something else....? please guide me

 

[azad092]

Here is a UDF (user defined function) that you can use...

Function NumCount(ByVal S As String) As Long
  Dim X As Long
  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)))
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NumCount just like it was a built-in Excel function. For example,

=NumCount(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

hi
thanks for reply
I have used your coding and formula as you guide me... but it only return 1
so please review it

 

[Rick Rothstein]

I have used your coding and formula as you guide me... but it only return 1

What text was in the cell that you passed into the function?

 

[azad092]

What text was in the cell that you passed into the function?

Hi Rick
thanks for reply
actually A1 was the column heading that's why it was not working properly.... but its a good work by you...
one thing more that I want that I want to a part of coding more.....for if there no numbers in the cell but have any text like a name etc than it should return the value 1
for examlple
in A2
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