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
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,128
with Power Query
Column1Count
1 ali, 2 asad , shan, 10 aslam3

Power Query:
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
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,052
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,435
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Here is a UDF (user defined function) that you can use...
VBA Code:
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

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

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

ADVERTISEMENT

with Power Query
Column1Count
1 ali, 2 asad , shan, 10 aslam3

Power Query:
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

Board Regular
Joined
Dec 31, 2019
Messages
152
Office Version
  1. 2007
Platform
  1. Windows
Here is a UDF (user defined function) that you can use...
VBA Code:
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
 

azad092

Board Regular
Joined
Dec 31, 2019
Messages
152
Office Version
  1. 2007
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,607
Messages
5,548,948
Members
410,884
Latest member
melifreeman
Top