How do I count cells beginning with specific letter?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

For example: I have a number of cells that contains names, and I have to
count the name that begins with letter "A", what do I do?

many thanks
 
I have a nuber of cells that contain names, I have to count the names that
begin with with letter, a,through z, How do I do that?
 
i think you mis understood, i need to know the number of first names in my
data, that begin with each particuliar letter of the alphabet, "A" through "z"

the number of occurances of each letter in the alphabet? A-Z

ex.

a 10
b 9
c 3
d 29
 
I think this is a John Walkenbach construct but not positive.

I have altered it return just the first letter of a string.

Sub Count_First_Letters()
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 1
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
 
Hi Jonathan

Just modify Dave's formula.
Type a, b , c etc down column C, then use

=COUNTIF(A:A,C1&"*")
Copy down through cells C2:C26
 
Back
Top