I have a countif function, using which I want to count those cells which have a numeric character anywhere.
Column ARich-Dilg-street 3I have 4 applesI have seven dogsHow 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)
Best Answer
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}&"*"))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)) ThenCell.Offset(0,1).Value = TrueExit ForEnd IfNext xNext CellYou 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 falseNow, count the total number of true values using countif
=countif(B:B,True)I am assuming that the strings contains non-negative numbers.
