Count the alphabetical letters in text

cameron_kyle

Active Member
Joined
Jan 14, 2006
Messages
313
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
try,

Code:
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

Active Member
Joined
Jan 14, 2006
Messages
313
looks great

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

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
okay, its untested at the mo

Code:
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

Active Member
Joined
Jan 14, 2006
Messages
313

ADVERTISEMENT

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


kyle
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
What's the difference between counting alphabetical letters and counting individual letters?
 

cameron_kyle

Active Member
Joined
Jan 14, 2006
Messages
313

ADVERTISEMENT

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

Active Member
Joined
Jun 27, 2006
Messages
345
Code:
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
OK, this assumes the text in Cell A1 and puts the results in C1:D26, change as necessary:

Code:
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

Active Member
Joined
Jan 14, 2006
Messages
313
the macro seem to have a problem with the below command it says argument is not optional


wheretoplace
 

Forum statistics

Threads
1,137,341
Messages
5,680,926
Members
419,945
Latest member
Carrie Sellers

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top