Count the alphabetical letters in text

[cameron_kyle]

i hope someone may help, this is a very spacific question which may prove to be quite difficult, I am trying to create a VB Script which will count the alphabetical letters in a paragraph of text,


Can anyone please advise me on the best metod of how this could be done, or refer me to a webpage which could help,


kyle

 

[patrickmuldoon99]

try,

Sub testy()
Dim myparagraph As String, letter_count As Long, a As Long
myparagraph = [a1]
letter_count = 0
a = 1
Do While a < (Len(myparagraph) + 1)
letter = Mid(myparagraph, a, 1)
If letter Like "[A-Za-z]" Then
    letter_count = letter_count + 1
End If
a = a + 1
Loop

End Sub

 

[cameron_kyle]

looks great

okay great, but how does it work were can i view the extracted data, at the moment it seem to fail

 

[patrickmuldoon99]

okay, its untested at the mo

Sub testy() 
Dim myparagraph As String, letter_count As Long, a As Long, letter as string
myparagraph = [a1] 
letter_count = 0 
a = 1 
Do While a < (Len(myparagraph) + 1) 
letter = Mid(myparagraph, a, 1) 
If letter Like "[A-Za-z]" Then 
    letter_count = letter_count + 1 
End If 
a = a + 1 
Loop 

msgbox("There were " & letter_count & " alphabetic characters in the paragraph")

End Sub

Ive added a messagebox at the end that will tell you how many alphabetic characters were counted in the given string in cell A1

All the code does is iterate through each character in the paragraph, and pattern matches it against a-zA-Z, and if that is true, adds one to letter_count. When the paragraph is finished, it will return a message box telling you how many characters it found.

Remember: The paragraph must be in cell A1 - otherwise you need to edit the code

HTH

 

[cameron_kyle]

okay everything seems to work great, however this scrip is counting all the aphabetical letters apposed to indavidual letters


kyle

 

[Scott Huish]

What's the difference between counting alphabetical letters and counting individual letters?

 

[cameron_kyle]

sorry i want to count all the a's that are present in the parragraph, then all the b's then all the c's d's, e's ............z's


the resoning for this is to create a graph of how many times different letters are used in certain historic novels,

 

[patrickmuldoon99]

option base 1

Sub testy() 
Dim myparagraph As String, letter_count As Long, a As Long, letter as string 
dim alpha_array(26) as long, alpha_ref as string
alpha_ref = Array(A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z)
myparagraph = [a1] 
letter_count = 0 
a = 1 
Do While a < (Len(myparagraph) + 1) 
letter = Mid(myparagraph, a, 1) 
If letter Like "[A-Za-z]" Then 
    letter_count = letter_count + 1 
    alpha_array(wheretoplace) = alpha_array(wheretoplace) + 1
End If 
a = a + 1 
Loop 

a=1
do while a < (ubound(alpha_array) + 1)

msgbox("There are " & alpha_array(a) & " instances of " & alpha_ref(a) & " in this paragraph")

a=a+1

loop

End Sub 

function wheretoplace(letter as string)
dim n as integer
dim alpha_refs(26) as string
alpha_refs = Array(A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z)
n=1
do until alpha_refs(n) = ucase(letter)
n=n+1
loop
wheretoplace = n
end function

Try above, run macro called testy - remember to use Cell A1

 

[Scott Huish]

OK, this assumes the text in Cell A1 and puts the results in C1:D26, change as necessary:

Sub LetterCount()
Dim r As Range, x As Integer, t As String
Set r = Range("A1")
t = UCase(r.Text)
For x = 1 To 26
    Range("C" & x) = Chr(64 + x) & "-"
    Range("D" & x) = Len(t) - Len(Replace(t, Chr(64 + x), ""))
Next x
End Sub

 

[cameron_kyle]

the macro seem to have a problem with the below command it says argument is not optional


wheretoplace