Count number of number and string in range

[Alpacino]

Hi all,

I have in range a5:a267 a list of number and names

Eg 1234 Chris
6734 Luke
8790 simon
0
0
Etc

I like to count the number of cell with the numbers and text in them.

So the example above would result in 3.

Thanks in advance

 

[ogo]

Hi,

Try the below code,

Dim iCount As Integer
 
Sub LoadString()
 
iCount = 0
 
For Row = 1 To 267
    CheckString = Cells(Row, 1).Value
    HasNumOrChr (CheckString)
Next Row
 
'Value of iCount will be the required output
MsgBox iCount
 
End Sub

Function HasNumOrChr(s As String) As Boolean
    Dim i As Integer
 
    For i = 1 To Len(s)
        If (Mid(s, i, 1) Like "[A-Z]" Or Mid(s, i, 1) Like "[a-z]") Then
 
        For j = 1 To Len(s)
 
            If Mid(s, j, 1) Like "[0-9]" Then
 
                HasOddChr = True
 
            End If
        Next j
 
       End If
 
    Next i
 
    If HasOddChr Then iCount = iCount + 1
 
End Function

Value of iCount will be the required output.

 

[Alpacino]

I did this by accident and it works
=COUNTIF(range,"<>0")