CODEWITHSUNDEEP
excelexcel-formula
Echchama Nayak
Echchama Nayak

Reputation: 933

Finding numeric characters using wildcards in countif

I have a countif function, using which I want to count those cells which have a numeric character anywhere.

Column A
Rich-Dilg-street 3
I have 4 apples
I have seven dogs

How do I write the countif criteria using wildcards in such a way that I can count those which have a numeric character? In the above example, the answer should 2 (1 and 2 not 3)

Upvotes: 4

Views: 3243

Answers (3)

aspan88
aspan88

Reputation: 617

You can use count, find and countif to get the desired result.

If the strings are in column A then

=count(FIND({0,1,2,3,4,5,6,7,8,9},A1))>0 will return True else false

Now, count the total number of true values using countif

=countif(B:B,True)

I am assuming that the strings contains non-negative numbers.

enter image description here

Upvotes: 0

user4039065
user4039065

Reputation:

Use a COUNTIF function with multiple wildcarded criteria.

=SUM(COUNTIF(A:A, {"*0*","*1*","*2*","*3*","*4*","*5*","*6*","*7*","*8*","*9*"}))

As pointed out by Scott Craner you can reduce the typing with,

=SUM(COUNTIF(A:A, "*"&{0,1,2,3,4,5,6,7,8,9}&"*"))

Upvotes: 11

Jordan
Jordan

Reputation: 4514

Try:

For Each Cell in Thisworkbook.Sheets(1).Range("A1:A10")
    For x = 1 to Len(Cell.Value)
        If IsNumeric(Mid(Cell.Value, x, 1)) Then
            Cell.Offset(0,1).Value = True
            Exit For
        End If
    Next x
Next Cell

Upvotes: 1

Related Questions