# Thread: Formula for field character search

1. Registered User
Join Date
Dec 2009
Posts
1

## Formula for field character search

Need help in writing formula for determining number of alphabetical characters in a field i.e. number of D's, E's, etc.

Assume this is simple but have not been able to figure out using help in EXCEL or MS Works.
Thank You.

2. Registered User
Join Date
Jan 2011
Posts
14

3. Registered User
Join Date
Jan 2002
Location
Bay Area
Posts
513
The attached Excel workbook shows an example of using VBA code to count the number of characters from A to Z found in a string of text. I can't image doing this with a formula in a cell.
The code converts lower case letters to upper case, so "a" is counted as "A".
Good luck.
Jerry

Code:
```Private Sub cmdRunCode_Click()
Dim strAlphabet As String, strText As String
Dim strLetter As String
Dim i As Integer, intTotal As Integer

strAlphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
strText = Cells(1, 1).Value

For i = 1 To Len(strAlphabet)
strLetter = Mid(strAlphabet, i, 1)
Cells(i, 2).Value = strLetter
Cells(i, 3).Value = Count_Letter(strLetter, strText)
totalLetters = totalLetters + Cells(i, 3).Value
Next i

Cells(1, 4).Value = "Total:"
Cells(1, 5).Value = totalLetters

End Sub

Private Function Count_Letter(Letter_To_Count As String, myText As String) As Integer
Dim j As Integer, ltrCount As Integer

For j = 1 To Len(myText)
If Letter_To_Count = UCase(Mid(myText, j, 1)) Then
ltrCount = ltrCount + 1
End If
Next j

Count_Letter = ltrCount

End Function```