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
精彩评论