How to count alphanumeric elements in excel?

Question

My series is like below in excel.

804
335123
335123
1210FC245
1210FC245
182663
1220FC140
700107
139562
MANUAL

The result should be '3' as it contains alphanumeric values but not only number or only characters.

Answer 1

I used 3 intermediary steps to arrive at the answer. I don't if this is the best way to solve this problem, but it certainly is one way.

Formula View:

---

Output:

Update

Here I've shortened the process by combine two of the previously used intermediary steps.

Formula View (V2):

The output remains the same, as expected.

Answer 2

Perhaps a User Defined Function (aka UDF).

Function countAphaNumeric(rng As Range) As Long
    Dim a As Long, n As Long, str As String, r As Range

    Set rng = Intersect(rng, rng.Parent.UsedRange)

    For Each r In rng
        str = r.Value2
        For a = 65 To 90
            If CBool(InStr(1, str, Chr(a), vbTextCompare)) Then Exit For
        Next a
        For n = 48 To 57
            If CBool(InStr(1, str, Chr(n), vbTextCompare)) Then Exit For
        Next n
        countAphaNumeric = countAphaNumeric - CBool(CBool(a <= 90) And CBool(n <= 57))
    Next r

End Function

Answer 3

With helper column in B2 write:

=--(SUMPRODUCT(COUNTIF(A2,"*"&ROW($A$1:$A$10)-1&"*"))>0)

Then just SUM your result

=SUM(B2:B1000)