Excel: Count how many text values in a column of data are Alpha only, Alphanumeric and All Types

Say I have a column with 10,000 entries. I want to count how many of these entries contain letters only, numbers and letters, and finall开发者_JAVA技巧y everything else. Note that all data is stored as text, not as numbers.

I tested the following and it seemed to work.

Note to get this to work you need to add a reference to Microsoft VBScript Regular Expressions 5.5 from within the VB editor:

Tools > References > Microsoft VBScript Regular Expressions 5.5

Sub CountTextPatterns()
Dim rngToCheck As Range
Dim cl As Range

Set rngToCheck = Range("A1:A10000") //Set up the range that contains the text data

Dim numbersLetters As VBScript_RegExp_55.RegExp
Set numbersLetters = New RegExp
numbersLetters.Pattern = "^\d*[a-zA-Z][a-zA-Z0-9]*$" //Regex pattern to match alphanumeric with at least one letter

Dim lettersOnly As VBScript_RegExp_55.RegExp
Set lettersOnly = New RegExp
lettersOnly.Pattern = "^[a-zA-Z]+$" //Regex pattern for letters only

Dim cntNumbersLetters As Long, cntLetters As Long, cntRemainder As Long
cntNumbersLetters = cntLetters = cntRemainder = 0

//Loop through range, match cell contents to pattern, and increment count accordingly
For Each cl In rngToCheck
    If lettersOnly.Test(cl) Then
        cntLetters = cntLetters + 1
    ElseIf numbersLetters.Test(cl) Then
        cntNumbersLetters = cntNumbersLetters + 1
    End If
Next

//For anything that isn't letters only or numbers and letters, simply subtract from counts and total row count
cntRemainder = rngToCheck.Rows.Count - cntNumbersLetters - cntLetters

Debug.Print "Numbers and Letters = " & cntNumbersLetters & vbTab & "Letters = " & cntLetters & vbTab & "Remainder = " & cntRemainder

End Sub