Find the Total of Alphabits in Excel

G

Gord Dibben

Sub CountLetters()
''a count of each letter in a range outputted to a new sheet
Dim letCount(1 To 26) As Long
Dim wkSht As Worksheet
Dim ii As Long
Dim Cell As Range
Dim WrkRng As Range
For Each wkSht In Worksheets
With wkSht
If .Name = "ListLetters" Then
Application.DisplayAlerts = False
Sheets("ListLetters").Delete
End If
End With
Next
Application.DisplayAlerts = True
Set WrkRng = ActiveSheet.UsedRange

For Each Cell In WrkRng
For ii = 1 To Len(Cell)
If Mid(UCase(Cell), ii, 1) Like "[A-Z]" Then
letCount(Asc(Mid(UCase(Cell), ii, 1)) - 64) = _
letCount(Asc(Mid(UCase(Cell), ii, 1)) - 64) + 1
End If
Next ii
Next Cell
Set CopytoSheet = Worksheets.Add
CopytoSheet.Name = "ListLetters"
CopytoSheet.Activate
Range("B1").Resize(26, 1).Value = Application.Transpose(letCount)
With Range("A1").Resize(26, 1)
.Formula = "=char(row()+64)"
.Value = .Value
End With

End Sub


Gord Dibben MS Excel MVP
 
D

Dave Curtis

Hi,

Not quite sure of the question, but this may help anyway.

If you want to know the total number of characters in a range, say A1:B10, use

=SUM(LEN(A6:B10)) entered with Control-Shift-Enter, as this is an array
formula.

Dave
 
Top